Post reply

Query using JOINS

  • 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

  • April 15, 2003 at 7:24 am

    #454216

    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

  • April 15, 2003 at 7:37 am

    #454223

    I mean an alternative query using keryword 'JOINS'

  • April 15, 2003 at 12:33 pm

    #454289

    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

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

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