|
|
Posted by Steve Jorgensen on 06/17/16 11:31
Since PERSON is on the many-side in both cases, it's easy. basically, this is
the case where you have several lookup values, each of which is optional.
SELECT PERSON.Name, SIGN.StarSignName
FROM PERSON LEFT JOIN
SIGN ON PERSON.StarSign = SIGN.StarSign
LEFT JOIN
FOOD ON PERSON.FavFood = FOOD.FavFood;
(presumably, this is hypothetical, and I don't need to mention table/field
naming issues)
On Wed, 9 Nov 2005 23:02:40 +0800, "Jack Smith" <jacksmith@nospam.co.uk>
wrote:
>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.
>
Navigation:
[Reply to this message]
|