Two different columns to one single column (JOIN)

Question

Two different columns to one single column (JOIN)

Fábio-917326

SSC Enthusiast

Points: 118
More actions
January 8, 2009 at 5:38 am

#133574

Hi fellow DB folks,
I've got a query that is puzzling me on how to make it work.
I have two tables that look like this:
Table 1 (Relations):
ID | User1 | User2
1 2051 3052
2 1032 2051
3 3052 1032
Table 2 (Users):
ID | Name
2051 The man
3052 The girl
1032 Someone in between
What I need is to develop a query that returns this:
ID | User1 | User2
1 The man The girl
2 Someone in between The man
3 The girl Someone in between
Normally I would do something like this:
SELECT rl.ID, us.Name FROM Relations rl, Users us
WHERE rl.User = us.ID
But now I have two columns on Table 1 that refer to the same column on Table 2.
How would I go to get the result above?
Thanks in advance for any help.
Regards,
Fábio

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

First, here are some tips for posting. Most people don't like to have to write all of the insert scripts to generate test data - spend the time to do that yourself.

http://www.sqlservercentral.com/articles/Best+Practices/61537/%5B/url%5D

And here is the solution:

[font="Courier New"]CREATE TABLE #tmpRelations (ID INT, User1 INT, User2 INT)

INSERT #tmpRelations VALUES (1,2051,3052)

INSERT #tmpRelations VALUES (2,1032,2051)

INSERT #tmpRelations VALUES (3,3052,1032)

GO

CREATE TABLE #tmpUsers (ID INT, Name VARCHAR(20))

INSERT #tmpUsers VALUES (2051,'The man')

INSERT #tmpUsers VALUES (3052,'The girl')

INSERT #tmpUsers VALUES (1032,'Someone')

GO

SELECT

*

FROM

#tmpRelations R

INNER JOIN #tmpUsers U1 ON U1.ID = R.User1

INNER JOIN #tmpUsers U2 ON U2.ID = R.User2

ORDER BY

1[/font]

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 2

You'll obviously need to reference table 2 more than once as if it were a separate table for User1 and User2 in table 1. To do that, just do a join based on that and give table 2 two different aliases.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

That old join syntax you are using (table names separated by commas in the FROM and the join fields in the WHERE) is really old. Switch to the new syntax (using the JOIN keyword) soon. Outer joins are already unsupported in the old syntax.

Fábio-917326 SSC Enthusiast Points: 118 More actions · Answer 4

Thanks Michael I will certainly do that next time I post something.

Thank you also Jeff, how could I not think about referencing the table twice :blink:

And Michael, yes, I'm aware of the oldness of the JOIN, it is just so much easier to do it that way, I also find it easier to read it that way.

Of course, when I need outer joins I do it on the new fashion.

Thanks anyway for your tips, I will stick for now with the old JOIN sintax, but will certainly consider using the JOIN ON sintax now that you mentioned it is a better practice.

Thanks all.

Regards,

Fábio

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 5

Fábio (1/8/2009)
Thank you also Jeff, how could I not think about referencing the table twice :blink:

You were probably thinking about alligators, instead... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Fábio-917326 SSC Enthusiast Points: 118 More actions · Answer 6

Jeff Moden (1/8/2009)
Fábio (1/8/2009)
Thank you also Jeff, how could I not think about referencing the table twice :blink:
You were probably thinking about alligators, instead... 😉

Hahahaha!!

No, I think I was thinking about thinking