Results 1 to 7 of 7

Thread: Query Advice - Probably simple

  1. #1
    Join Date
    May 2008
    Posts
    6

    Query Advice - Probably simple

    Hi,
    Quite new to SQL. I have been asked to query two tables to find which combinations of CustomerID and ProductID exist in one but not the other. I am concatenating the fields in the query to produce a combination I can query ( see below)

    This seems to work but takes along time - I'm sure there must be a better way of doing this? Please bear in mind I am a novice in your responses!

    select distinct(ProductID),CustomerID from customerpackproduct where

    str(ProductID) + str(CustomerID) not in

    (select str(ProductID) + str(CustomerID) from customerproduct)

    order by customerid

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    You can use LEFT OUTER JOIN customerpackproduct with customerproduct on ProductID and CustomerID. Any rows with NULLs in these two columns are the rows you are looking for.

  3. #3
    Join Date
    May 2008
    Posts
    6
    Thanks,

    Could you demonstrate the syntax for me?

  4. #4
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Look in books online for syntax.

    select distinct cpp.ProductID, cpp.CustomerID from customerpackproduct as cpp
    left outer join customerproduct as cp
    on cpp.ProductID = cp.ProductID
    and cpp.CustomerID = cp.CustomerID
    where cp.CustomerID is NULL
    and cp.ProductID is NULL
    order by cpp.customerid

  5. #5
    Join Date
    May 2008
    Posts
    6
    Many thanks for your help

  6. #6
    Join Date
    Jun 2008
    Posts
    2
    I think full outer join would be better in this instance, as both tables can be checked in a single query...

    select distinct cpp.ProductID AS cpp_ProductId, cpp.CustomerID AS cpp_CustomerID, cp.ProductId AS cp_ProductID, cp.CustomerID AS cp_CustomerID from customerpackproduct as cpp
    full outer join customerproduct as cp
    on cpp.ProductID = cp.ProductID
    and cpp.CustomerID = cp.CustomerID
    where (cp.CustomerID is NULL and cp.ProductID is NULL)
    OR (cpp.customerId IS NULL AND cpp.productId IS NULL)

  7. #7
    Join Date
    Jul 2008
    Posts
    3

    Reverting the Query

    select str(ProductID), str(CustomerID)
    from customerproduct a
    where EXISTS
    (Select 1
    from customerpackproduct b
    where a.CustomerID=b.CustomerID
    AND a.ProductID=b.ProductID)
    order by str(CustomerID)

    One more thing ... This will work the fastest if u have indexing On CustomerId and ProductId in customerpackproduct table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •