Help me for these sql line case please

[Guest]

Hi guys

I'm newbie on sql structure and syntax.
And I have this problem :
I have 3 table :

Table_A
FieldA1 FieldA2
a01 John
a02 James
a03 Bob
a04 Sandra

Table_B
FieldB1 FieldB2
b01 Desk
b02 Chair
b03 Sofa
b04 Saddle
b05 Cradle

Table_C
FieldC1 FieldC2
c01 Computer
c02 Phone
c03 Calculator
c04 VCR
c05 TV
c06 DVD
c07 CD

I want to manage those tables with the queries and
the output datasheet will look like this

FieldA1 FieldA2 FieldB1 FieldB2 FieldC1 FieldC2 FieldD1
a01 John b02 Chair c04 VCR
a01b02c04
c06 DVD
a01b02c06
c07 CD
a01b02c07
a02 James b03 Sofa c01 Computer a02b03c01
b04 Saddle c02 Phone
a02b03c02
c05 TV
a02b03c05
a03 Bob b01 Desk c03 Calculator
a03b01c03
c05 TV
a03b01c05
a04 Sandra b05 Cradle c06 DVD a04b05c06

How many queries should I make ?
Please help me the sql statement too ....

Thanks ...

 

[Jason Lepack]

How many queries should I make ?

None! What piece of data exists in these tables that links them to
each other? I see three sets of data that are totally unrelated to
each other.

You could implement a Cartesian join but I don't think that the result
of that is what you are looking for...

Actually, on further inspection, it may be what you want, so here it
is, if it's not, then I think you should explain WHY you want to do
this.

Insert the following code in SQL View (View -> SQL) in a new Query:

SELECT
FieldA1, FieldA2,
FieldB1, FieldB2,
FieldC1, FieldC2,
FieldA1 & FieldB1 & FieldC1 AS FieldD1
FROM
Table_A, Table_B, Table_C

Please note that this query will return 4*5*7 = 140 records just for
this simple example that you gave here.

Cheers,
Jason Lepack

 

[Guest]

Hi

Thank to you Jason for reply my question,
and remember me to show the relationship.
The table result should only have 9 records.
I will tell you the relation between the tables :

Table_B have relationship to Table_A on FieldB3
Table_D have realtionship to Table_A on FieldD1
Table_D have relationship to Table_C on FieldD2

I would like to try by the UNION command, but that will result more than 9
records.
Altough the meaning is the same with the UNION, but it will be take
efficiency to read, save, or print, and more ...

I will read the records like this :
"John on Chair with VCR, DVD and CD"
"James on Sofa and Saddlee with Computer, Phone and TV"
"Bob on Desk with Calculator and TV"
"Sandra on Cradle with DVD"

These are the tables and fields :

Table_A
FieldA1 FieldA2
a01 John
a02 James
a03 Bob
a04 Sandra

Table_B
FieldB1 FieldB2 FieldB3
b01 Desk a03
b02 Chair a01
b03 Sofa a02
b04 Saddle a02
b05 Cradle a04

Table_C
FieldC1 FieldC2
c01 Computer
c02 Phone
c03 Calculator
c04 VCR
c05 TV
c06 DVD
c07 CD

Table_D
FieldD1 FieldD2
a01 c04
a01 c06
a01 c07
a02 c01
a02 c02
a02 c05
a03 c03
a03 c05
a04 c06

Query_A
FieldA1 FieldA2 FieldB1 FieldB2 FieldC1 FieldC2 FieldD1
a01 John b02 Chair c04 VCR a01b02c04
c06 DVD
a01b02c06
c07 CD
a01b02c07
a02 James b03 Sofa c01 Computer a02b03c01
b04 Saddle c02 Phone a02b03c02
c05 TV
a02b03c05
a03 Bob b01 Desk c03 Calculator a03b01c03
c05 TV
a03b01c05
a04 Sandra b05 Cradle c06 DVD a04b05c06



Thank you,
Helgeduelbeck

 

[Jason Lepack]

this is the best that I can do with a simple query.. It actually
returns twelve records.

SELECT Table_A.FieldA1, Table_A.FieldA2, Table_B.FieldB1,
Table_B.FieldB2, Table_C.FieldC1, Table_C.FieldC2, Table_D.FieldD1
FROM Table_C INNER JOIN ((Table_A INNER JOIN Table_B ON
Table_A.FieldA1 = Table_B.FieldB3) INNER JOIN Table_D ON
Table_A.FieldA1 = Table_D.FieldD1) ON Table_C.FieldC1 =
Table_D.FieldD2;

FieldA2 FieldB2 FieldC2
John Chair VCR
John Chair DVD
John Chair CD
James Sofa Computer
James Saddle Computer
James Sofa Phone
James Saddle Phone
James Sofa TV
James Saddle TV
Bob Desk Calculator
Bob Desk TV
Sandra Cradle DVD

Cheers,
Jason Lepack

 

[Guest]

Yeah, that's the best we both can do.

But if you could imagine,
If these records :

James Sofa Computer
James Saddle Computer
James Sofa Phone
James Saddle Phone
James Sofa TV
James Saddle TV

become :

James Sofa Computer
Saddle Phone
TV

It would be a big help for the huge data records to manage

Jason, I still have unsolved problem then,
but I preciate your answers and thank u a lot.

Regards ...
Helgeduelbeck