Query using JOINS

Question

Post reply

Query using JOINS

jma100

Mr or Mrs. 500

Points: 576
More actions
April 15, 2003 at 6:59 am

#80065

Hallo friends
Is there any alternative for following query using JOINS
select * from Products where ProductID not in (select ProductID From InvoicePos)
Best regards
Joseph

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeremy Kemp SSCertifiable Points: 6754 More actions · Answer 1

A couple of ways:

select *

from products

where not exits (select productId from InvoicePos

where products.productId = invoices.productID)

or

select *

from products a

where 0 =

(select count(*)

from InvoicePos b

where a.productID = b.productID)

The first query would be quicker in this case because the sub query stops as soon as it finds a match - in the second one it counts all the rows. The second query would be useful it you need to find products which are in more than 20 invoices.

Jeremy

Edited by - Jeremy Kemp on 04/15/2003 07:24:46 AM

jma100 Mr or Mrs. 500 Points: 576 More actions · Answer 2

I mean an alternative query using keryword 'JOINS'

SJTerrill SSCrazy Points: 2938 More actions · Answer 3

If I understand you correctly, try this:



SELECT        Products.*

FROM          Products

              LEFT OUTER JOIN

              InvoicePos

              ON Products.ProductID = InvoicePOS.ProductID

WHERE         InvoicePOS.ProductID IS NULL

HTH,

SJTerrill