Help me to design a query - 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 2000 Forums

SQL Server Development (2000)

Help me to design a query

Author

Topic

Petehe
Starting Member

20 Posts

Posted - 2002-10-14 : 18:50:40

Table 1
***********
ColourID
ColourName

Table 2
************
ColourID1 //link to ColourID
ColourID2 //link to ColourID
ColourID3 //link to ColourID

Result Table
************
ColourName1 //tne colourname correspond to colourID1
ColourName2 //tne colourname correspond to colourID2
ColourName3 //tne colourname correspond to colourID3

How to write a sql to generate the result table from Table1 and Table2

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-10-14 : 19:10:51

SELECT Table1.ColourName
FROM Table1 INNER JOIN Table2 On Table1.ColourID = Table2.ColourID

Petehe
Starting Member

20 Posts

Posted - 2002-10-14 : 19:13:20

Thanks for your reply, but I need show three Colour Names in the Result Table.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-14 : 19:17:47

Which is exactly what Valter's query will provide for you.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-10-14 : 19:35:13

If you need to combine multiple rows into one.
(ie)
color1, color2, color3

Read this:
http://www.sqlteam.com/item.asp?ItemID=11021

Petehe
Starting Member

20 Posts

Posted - 2002-10-14 : 19:36:35

Sorry, I still can't figure it out.
I tried your example but SQL server say, Table2 don't have column ColourID

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-10-14 : 19:46:58

Try this:

SELECT A.ColourName, B.ColourName, C.ColourName
FROM Table2 As D LEFT JOIN Table1 As A On D.ColourID1 = A.ColourID LEFT JOIN Table1 As B On D.ColourID2 = B.ColourID
LEFT JOIN Table1 As C On D.ColourID3 = C.ColourID

This will show your data like
colourName1, colourName2, colourName3

This will return the data like you show in the result table but unless you filter or you only have 1 row then it's going to give you more than one row.

SELECT A.ColourName
FROM Table2 As D INNER JOIN Table1 As A On D.ColourID1 = A.ColourID
UNION ALL
SELECT B.ColourName
FROM Table2 As D INNER JOIN Table1 As B On D.ColourID1 = B.ColourID
UNION ALL
SELECT C.ColourName
FROM Table2 As D INNER JOIN Table1 As A On D.ColourID1 = C.ColourID

Enjoy

I have a feeling you would benefit from a better design but I really can't say anymore without knowing a little more of what your trying to do.

Edited by - ValterBorges on 10/14/2002 19:48:24

Petehe
Starting Member

20 Posts

Posted - 2002-10-14 : 19:53:28

Great, it works, thank you for your help.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-10-15 : 19:42:58

Petehe, when you have a field name repeat itself in a table (e.g. ColourID1, ColourID2, ColourID3) it suggests that your schema is not properly normalized. You don't mention what other fields are in your table, but assuming that Table2 has some other field as the Primary Key, then a new table with Table2.PK and ColourID would be in order. Then you'd only have to join Table1 once to that new table to get all the colors. And, even more important, if you decided that a 4th color ID was needed, it's just a matter of adding another row to the new table instead of rebuilding your existing table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources