CODEWITHSUNDEEP
sqljoin
LastDavid
LastDavid

Reputation: 101

How can I use two columns from the result of one query to look up the value(s) in another table?

How can I use two columns from the result of one query to look up the value(s) in another table?

I have seen several examples of the "dictionary" problem, using a single column, like UserID to look up the user's First and Last Name, but I have two columns that both need to match with multiple possible results for each match.

Currently, I query for all records that are TRUE for Ready to Ship, then use code to loop through the results looking up the Shipping Label Files for each Product/Color combanation. My goal is get the desired results with a single query.

I tried the following SQL but it is too slow (several minutes). I was wondering if the use of JOIN or some other trick might make this quicker (Less than a second). Currently my code take ~2 seconds. The Real World tables I am using are several thousand records long and return a few hundred results.

Select
  tblA.BoxID,
  tblA.Product,
  tblA.Color,
  tblA.Ready_to_Ship AS Ready,
  tblB.Shipping_Label_File
From
  Table_A tblA,
  Table_B tblB
Where
  tblB.Product = tblA.Product AND
  tblB.Color = tblA.Color AND
  tblA.Ready_to_Ship = 'TRUE'


Desired results:
BoxID    Product        Color  Ready   Shipping_Label_File
B5255    34xBty2001     Red    TRUE    ShipLBL-01r_A.txt
B5255    34xBty2001     Red    TRUE    ShipLBL-01r_B.txt
J6632    34xBty2002     Blue   TRUE    ShipLBL-07b_D.txt
E2748    34xBty2002     Red    TRUE    ShipLBL-07r_D.txt
E4716    64d_Dty2005    Red    TRUE    ShipLBL-05r_B.txt
E4716    64d_Dty2005    Red    TRUE    ShipLBL-05r_C.txt

Table_A
BoxID    Product       Color  Ready_to_Ship
B5255    34xBty2001    Red    TRUE
J6632    34xBty2002    Blue   TRUE
F8975    64b_Dty2005   Blue   FALSE
F9768    64b_Dty2005   Blue   FALSE
I1053    34xBty2001    Green  FALSE
J2202    34xBty2001    Blue   FALSE
D2986    64a_Dty2005   Blue   FALSE
A6210    64b_Dty2005   Blue   FALSE
I1088    34xBty2002    Blue   FALSE
E2748    34xBty2002    Red    TRUE
D7945    64b_Dty2005   Blue   FALSE
E4716    64d_Dty2005   Red    TRUE

Table_B
Product      Color   Shipping_Label_File
34xBty2001   Red     ShipLBL-01r_A.txt
34xBty2001   Red     ShipLBL-01r_B.txt
34xBty2001   Blue    ShipLBL-01b_A.txt
34xBty2001   Green   ShipLBL-01g_A.txt
34xBty2001   Green   ShipLBL-01g_C.txt
34xBty2002   Red     ShipLBL-07r_D.txt
34xBty2002   Blue    ShipLBL-07b_D.txt
34xBty2002   Green   ShipLBL-07g_M.txt
64a_Dty2005  Blue    ShipLBL-A3b_A.txt
64a_Dty2005  Green   ShipLBL-A3g_E.txt
64b_Dty2005  Red     ShipLBL-05r_B.txt
64b_Dty2005  Red     ShipLBL-05r_C.txt
64b_Dty2005  Green   ShipLBL-05g_A.txt

Upvotes: 1

Views: 174

Answers (1)

valex
valex

Reputation: 24144

Try

Select
  tblA.BoxID,
  tblA.Product,
  tblA.Color,
  tblA.Ready_to_Ship AS Ready,
  tblB.Shipping_Label_File
From
  Table_A tblA 
  left join Table_B tblB on tblA.Product=tblB.Product and   tblA.Color=tblB.Color
Where
  tblA.Ready_to_Ship = 'TRUE'

Upvotes: 1

Related Questions