Subquery with 2 fields

[minli98]

Hi,

Say I want to do a query to look up all products (and their info) that meet certain criteria in the Sales table. I would write it:

select *
from product
where productId in 
  (select productId from Sales where <Condition>)

But what if I want to match the subquery with the productId and the colorId. What is the right syntax for something like the following:

select *
from product
where (productId, colorId) in 
   (select productId, colorId from Sales where <Condition>)

Is this even possible or do I have to do a join instead?

Many thanks in advance.

Regards,
Min

 

[bborissov]

select product.*
from product
INNER JOIN 
 (select productId, colorId from Sales where <Condition>) Tbl1
ON product.productId = Tbl1.productId AND
   product.colorId   = Tbl1.colorId

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[roccorocks]

That looks right from boris. If you need a good time fix check out this link thread183-949965

 

[minli98]

Thanks Boris. I will give that a try.

Regards,
Min

 

[minli98]

Hi,

Sorry, I just realized that I asked the wrong question. It should have been like this:

select *
from product
where (productId, colorId) not in
   (select productId, colorId from Sales where <Condition>)

So, I am looking for the product and color ids that are not among those that meet certain condition.

How would I do this? Thank you once again.

Regards,
Min

 

[Onyxpurr]

select product.*
from product
LEFT JOIN
(select productId, colorId from Sales where <Condition>) Tbl1
ON product.productId = Tbl1.productId AND
product.colorId = Tbl1.colorId
WHERE tbl1.productid is null

 

[minli98]

Hi Onyxpurr,

I tried it and it works! Thank you. Would have never thought of it myself.

Min

 

[thendrickson]

Or try a theta join

select product.*
from product
INNER JOIN
(select productId, colorId from Sales where <Condition>) Tbl1
ON product.productId <> Tbl1.productId AND
product.colorId <> Tbl1.colorId

And yes, I just like using a term I just learned recently

 

[r937]

And yes, I just like using a term I just learned recently

you might want to try testing that query, because i don't think you learned how to apply the term correctly

r937.com | rudy.ca

 

[thendrickson]

If I did not misread the corrected post my query should work.

My query should return all from product where product id and colorid are not in the sub select.I will test it once I get to a pc with SQL Server installed