How to do a JOIN statement for a table with 2, one-to-many relationships.

[Jack Smith]

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.StarSignNa me
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.

[Steve Jorgensen]

Re: How to do a JOIN statement for a table with 2, one-to-many relationships.

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.StarSignNa me
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@nosp am.co.uk>
wrote:
[color=blue]
>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.StarSignNa me
>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.
>[/color]

[Jack Smith]

Re: How to do a JOIN statement for a table with 2, one-to-many relationships.

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:4a54n11jpo d53n6k9n3j401kn m7d90419v@4ax.c om...[color=blue]
> 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.StarSignNa me
> 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@nosp am.co.uk>
> wrote:
>[color=green]
>>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)
>>StarSignNam e varchar(50)
>>
>>Relationshi p: 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.StarSignNa me
>>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)
>>
>>Relationshi p: 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.
>>[/color]
>[/color]

[Erland Sommarskog]

Re: How to do a JOIN statement for a table with 2, one-to-many relationships.

Jack Smith (jacksmith@nosp am.co.uk) writes:[color=blue]
> Thank-you! One thing though if possible - can you repost your solution,
> but nest the brakets around the joins.[/color]

I'd rather not...

Personally, I would write Steve's solution as:

SELECT P.Name, S.StarSignName, F.FoodName
FROM PERSON P
LEFT JOIN SIGN S ON P.StarSign = S.StarSign
LEFT JOIN FOOD F ON P.FavFood = F.Food;

You can add parentheses to your heart's content, but for this query
it would add more confusion than necessary.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp