Win a copy of The Business Blockchain this week in the Cloud forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Al Koch
Ranch Hand
Posts: 48
Eclipse IDE Firefox Browser Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,


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:

- TableColor: this is a table of colors that looks like:

- TableClothes: this is a table that logs the pants and shirt color worn each day that looks like:


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


This query gets me close:



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.


 
K. Tsang
Bartender
Posts: 3583
16
Android Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Your original sql close since you got the pants color working.

To get the shirt color, you can use the same sql (sort of) and join with itself.

select a.Day, a.PantsColorID, a.PantsColor, b.ShirtColorID, b.ShirtColor
from
/* pants sql */
(select tableclothes.Day,
tableclothes.PantsColorID, tablecolor.Color as PantsColor
from tableclothes, tablecolor
where tableclothes.PantsColorID = tablecolor.ColorID) a,
/* shirt sql */
(select tableclothes.Day,
tableclothes.ShirtColorID, tablecolor.Color as ShirtColor
from tableclothes, tablecolor
where tableclothes.ShirtColorID = tablecolor.ColorID) b
where a.Day=b.Day
 
Al Koch
Ranch Hand
Posts: 48
Eclipse IDE Firefox Browser Tomcat Server
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks.

Here is perhaps an even better solution:

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 ;


 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic