SELECT with 2 tables - 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

General SQL Server Forums

New to SQL Server Programming

SELECT with 2 tables

Author

Topic

niavlys
Starting Member

1 Post

Posted - 2011-03-29 : 11:02:20

Hi,
It seems simple but I can't figure out how to do this. I have 2 tables (Printers and Cartridges). Here is the basic structure:


Printers                   Cartridges

ID  (Primary Key)          idCartridge
idBlack                    cartNumber
idCyan                     
idYellow
idMagenta

I want to select all printers from table Printers but instead of having the ids for the cartridges, I want to link the tables to have the cartNumber instead.
I can't use an Inner Join because idBlack, idCyan, idYellow and idMagenta all contain the value I want to get from the Cartridges table.

I hope someone understands my problem. It's hard to explain.

Thanks for your help.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-29 : 11:17:15

SELECT P.ID, Black.cartNumber BlackCartridge,
Cyan.cartNumber CyanCartridge,
Yellow.cartNumber YellowCartridge,
Magenta.cartNumber MagentaCartridge
FROM Printers P
LEFT JOIN Cartridges Black ON P.idBlack=Black.idCartridge
LEFT JOIN Cartridges Cyan ON P.idCyan=Cyan.idCartridge
LEFT JOIN Cartridges Yellow ON P.idYellow=Yellow.idCartridge
LEFT JOIN Cartridges Magenta ON P.idMagenta=Magenta.idCartridge

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources