CODEWITHSUNDEEP
sql
Al Koch
Al Koch

Reputation: 423

How define a SQL query to "lookup and return a value from a table twice"

I do not know how to formulate a query. To simplify the problem I have created a silly but simple example. Here are the specifics:

ColorID       Color
1             Red
2             Green
3             Blue

Day       PantsColorID        ShirtColorID
  1       2                   3           (Day 1 wore green pants and a blue shirt)
  2       3                   1           (Day 2 wore blue pants and a red shirt)

How do I define a query to return a dataset that looks like:

Day   PantsColorID    PantsColor  ShirtColorID    ShirtColor
1     2               Green       3               Blue
2     3               Blue        1               Red

This query gets me close:

select 
    TableClothes.Day, 
    TableClothes.PantsColorID, 
    TableColor.Color as 'color of pants',
    TableClothes.ShirtColorID, 
    TableColor.Color as 'color of shirt', 
    TableColor.ColorID  
from TableClothes, TableColor 
where TableClothes.PantsColorID = TableColor.ColorID

Day   PantsColorID    PantsColor  ShirtColorID    ShirtColor
1     2               Green       3               Green
2     3               Blue        1               Blue

Of course, this query returns the correct pants color but is shows that same color as the shirt color which is wrong.

How do I construct a query to return the correct color for both pants and shirt?

Thank you.

Upvotes: 0

Views: 544

Answers (5)

RobP
RobP

Reputation: 9532

You need to JOIN the TableClothes table against the Colors table twice, using different conditions and a different alias for the Colors table each time. 

SELECT Day, 
PantsColorID, PantsColors.Color AS PantsColor, 
ShirtColorID, ShirtColors.Color AS ShirtColor
FROM TableClothes 
JOIN TableColor PantsColors ON TableClothes.PantsColorID = PantsColors.ColorID
JOIN TableColor ShirtColors ON TableClothes.ShirtColorID = ShirtColors.ColorID

Upvotes: 2

Zane
Zane

Reputation: 4169

First off. You're using the old style joins which is bad for all of these reasons.

Second, you have identifiers with spaces (not the best choice, you could use underscores instead of spaces) and even worse, you're using single quotes for these identifiers: 'color of pants' which is not ANSI standard, quite confusing as single quotes are used for string literals and also under deprecation. See another set of good reasons. So, it's better to use double quotes (or brackets) for identifiers: "color of pants".

Third, we'll add the statement separator (;) because it should be obvious where a statement ends and also because SQL-Server while being happy to allow you not place these separators and do the dirty job of finding out where a statements ends and another starts, it will get confused when the next statement starts with WITH. Help him (and the next developer who will be reading your code) to stay sane.

So if we fix those, your query will look like this:

select 
    TableClothes.Day, 
    TableClothes.PantsColorID, 
    TableColor.Color as "color of pants",
    TableClothes.ShirtColorID, 
    TableColor.Color as "color of shirt", 
    TableColor.ColorID  
from TableClothes
inner join TableColor 
    on TableClothes.PantsColorID = TableColor.ColorID ;

The reason this isn't giving you the results you crave is you also need to join to both the shirt and pants colorID that way you can get the descriptor information for both.

select 
    TableClothes.Day, 
    TableClothes.PantsColorID, 
    TableColor.Color as "color of pants",
    TableClothes.ShirtColorID, 
    TableColor.Color as "color of shirt", 
    TableColor.ColorID  
from TableClothes
inner join TableColor 
    on TableClothes.PantsColorID = TableColor.ColorID
inner join TableColor   
    on TableClothes.ShirtColorID = TableColor.ColorID

Oh but wait that doesn't compile. That's because when you refer to TableColor twice like this the Database System has no clue which one you are referring to in your SELECT and JOIN statements. So we're going to use a technique called aliasing that will not only solve for this but make your code easier to read.

select 
    C.Day, 
    C.PantsColorID, 
    P.Color as 'color of pants',
    C.ShirtColorID, 
    S.Color as 'color of shirt', 
    C.ColorID  
from TableClothes as C
inner join TableColor as P
    on C.PantsColorID = P.ColorID
inner join TableColor as S
    on C.ShirtColorID = S.ColorID ;

There now we have a functional clean easy to read query.

Upvotes: 7

Hogan
Hogan

Reputation: 70538

You use an alias -- like this:

select TableClothes.Day, 
       TableClothes.PantsColorID, 
       Color1.Color as 'color of pants' ,
       TableClothes.ShirtColorID, 
       Color2.Color as 'color of shirt', 
from TableClothes, TableColor as Color1, TableColor as Color2
where TableClothes.PantsColorID = Color1.ColorID
 and  TableClothes.ShirtColorID = Color2.ColorID

More common to use modern join syntax (which I think makes it clearer)

select TableClothes.Day, 
       TableClothes.PantsColorID, 
       TableColor.Color as 'color of pants' ,
       TableClothes.ShirtColorID, 
       Color2.Color as 'color of shirt', 
from TableClothes, 
join TableColor as Color1 on TableClothes.PantsColorID = Color1.ColorID
join TableColor as Color2 on TableClothes.ShirtColorID = Color2.ColorID

Upvotes: 4

Astra Bear
Astra Bear

Reputation: 2738

You need to join the Clothes table to the Color table twice. Like

select * from TableClothes L, TableColor C1, TableColor C2 where
L.shirtcolorid = c1.colorid and L.pantscolorid = c2.colorid

Upvotes: -2

Alexis Peters
Alexis Peters

Reputation: 1631

SELECT TableClothes.Day, 
  TableClothes.PantsColorID, 
  t1.Color as PantsColor ,
  TableClothes.ShirtColorID, 
  t2.Color as ShirtColor,
  TableColor.ColorID  
from TableClothes

LEFT JOIN TableColor as t1
ON TableColor.ColorId = TableClothes.PantsColorID

LEFT JOIN TableColor as t2
ON TableColor.ColorId = TableClothes.ShirtColorID

Upvotes: -2

Related Questions