Cross Referencing Column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Cross Referencing Column

Author

Topic

hallen
Starting Member

2 Posts

Posted - 2012-07-27 : 19:00:21

TWO TABLES: CarSales, Users

CarSales
carsaleid buyerid sellerid
1 1 100
2 2

Users
UserID FullName
1 Joe
2 Bob
100 Ryan

buyerid, sellerid both FKs for UserID in the User Table.

ok, now, retrieve a list of the sales with the buyer and seller fullnames.

Easy right?

SELECT carsaleid, U.FullName FROM CarSales AS CS
LEFT JOIN Users AS U
ON CS.BuyerID = U.UserID

But how do I also get the fullname of the seller ? :)

singularity
Posting Yak Master

153 Posts

Posted - 2012-07-27 : 19:24:05

[code]
SELECT carsaleid, U.FullName as BuyerName, V.FullName as SellerName
FROM CarSales AS CS
LEFT JOIN Users AS U
ON CS.BuyerID = U.UserID
LEFT JOIN Users AS V
ON CS.SellerID = V.UserID
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-27 : 20:25:46

why left join? a sale will always have buyer and seller right then you could use inner join rather than a left join

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

hallen
Starting Member

2 Posts

Posted - 2012-07-28 : 01:11:12

Thank you!! Exactly what I wanted! :)

quote:

Originally posted by singularity


SELECT carsaleid, U.FullName as BuyerName, V.FullName as SellerName 
FROM CarSales AS CS
LEFT JOIN Users AS U
ON CS.BuyerID = U.UserID
LEFT JOIN Users AS V
ON CS.SellerID = V.UserID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-28 : 12:46:51

quote:
Originally posted by hallen

Thank you!! Exactly what I wanted! :)

quote:
Originally posted by singularity

SELECT carsaleid, U.FullName as BuyerName, V.FullName as SellerName
FROM CarSales AS CS
LEFT JOIN Users AS U
ON CS.BuyerID = U.UserID
LEFT JOIN Users AS V
ON CS.SellerID = V.UserID

Hope you saw what I suggested

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources