468,283 Members | 1,851 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,283 developers. It's quick & easy.

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

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.
Nov 9 '05 #1
3 1407
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" <ja*******@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.


Nov 9 '05 #2
Thank-you! One thing though if possible - can you repost your solution, but
nest the brakets around the joins.

Final thanks...
Jack.

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:4a********************************@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" <ja*******@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.

Nov 9 '05 #3
Jack Smith (ja*******@nospam.co.uk) writes:
Thank-you! One thing though if possible - can you repost your solution,
but nest the brakets around the joins.


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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Nov 9 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by eXavier | last post: by
3 posts views Thread by rjaw | last post: by
5 posts views Thread by gimme_this_gimme_that | last post: by
6 posts views Thread by GarryJones | last post: by
2 posts views Thread by ltansey | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.