Posted by Jack Smith on 11/28/32 11:31

Thank-you! One thing though if possible - can you repost your solution, but
nest the brakets around the joins.

Final thanks...
Jack.

"Steve Jorgensen" <nospam@nospam.nospam> wrote in message
news:4a54n11jpod53n6k9n3j401knm7d90419v@4ax.com...
> 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.
>>
>

[Back to original message]