Posted by Jack Smith on 11/12/60 11:31

Hello,

I want to be able to view data from 3 tables using the JOIN statement, but
I'm not sure of how to do it. I think i don't know the syntax of the joins.I
imagine this is easy for the experienced - but Im not.

Allow me to explain:
I have 2 Tables: PERSON and SIGN

PERSON
------
PersonNo int (Primary Key)
Name varchar(50)
StarSign int
FavFood int

SIGN
----
StarSign int (Primary Key)
StarSignName varchar(50)

Relationship: SIGN has a one-to-many relationship with PERSON. The linking
field is called 'StarSign'.

Question 1:
I want to display all the peoples names, and their star sign (whether they
have one or not).
Answer 1:
SELECT PERSON.Name, SIGN.StarSignName
FROM PERSON LEFT OUTER JOIN SIGN ON PERSON.StarSign = SIGN.StarSign;

No problems there. But now I want to do the same thing, but have their
favourite food displayed as well. So an additional table is needed:

FOOD
----
FavFood int (Primary Key)
FavFoodName varchar(50)

Relationship: FOOD has a one-to-many relationship with PERSON. The linking
field is called 'FavFood'.

Question 2:
I want to display all the peoples names, their star signs (whether they
have one or not), and their favourite food (whether they have one or not).
Answer 1:
???

I'm not sure what to do. Notice that I want to use an LEFT OUTER JOIN so ALL
the rows from table PERSON will appear 'irrespective' of whether they have
related records in the other tables.

Jack.

• Next in forum: How to do a JOIN statement for a table with 2, one-to-many relationships.
• Prev in forum: How to do a JOIN statement for a table with 2, one-to-many relationships.
• Thread view: How to do a JOIN statement for a table with 2, one-to-many relationships.

[Reply to this message]