CODEWITHSUNDEEP
sql-serverms-access
user1012598
user1012598

Reputation: 427

Getting records from one table not in another

I have a table, called Prices composed of a few fields, two of which is ProductId and PriceType. Some products have both Wholesale price (PriceType = W) and Retail price (PriceType = R), i.e. they have two records with the same product id.

I would like to display to the user those products with no retail price (i.e. those with only one record W).

How should I write my query to do this?

I have used something like 

Select * from Products 
INNER JOIN Prices ON Products.Id = Prices.ProductId 
WHERE ProductId NOT IN 
  (SELECT ProductId FROM Prices WHERE PriceType="R")

but this is executing very slow. I have checked and verified that the part taking a long time is really the NOT IN clause.

Any ideas?

Upvotes: 2

Views: 2309

Answers (2)

Jaimal Chohan
Jaimal Chohan

Reputation: 8645

SELECT * 
FROM Products 
INNER JOIN Prices 
        ON Products.Id = Prices.ProductId 
       AND Prices.PriceType = 'W'

This will filter out the Prices at the join so you never have any PriceType='R' records joined.

To filter out those records that do not have an 'R' at all, you can use the following query, which removes the sub-select on your where clause

SELECT 
    Products.ProductId
FROM 
    Products 
    INNER JOIN Prices A
        ON Products.ProductId = A.ProductId 
        AND A.PriceType = 'W'
    LEFT JOIN Prices B
        ON A.ProductId = B.ProductId 
        AND B.PriceType <> A.PriceType    
WHERE
    A.PriceType = 'W'
    AND B.PriceId is null

To ensure this runs as fast as possible, ensure you add an index to your Prices table that includes both the ProductId and PriceType columns

Upvotes: 2

Niranjan Singh
Niranjan Singh

Reputation: 18290

I think you are looking for Left Excluding JOIN, check Visual Representation of SQL Joins

SELECT <select_list> 
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key
WHERE B.Key IS NULL

so your query should be:

Select * from Products 
Left JOIN Prices ON Products.Id = Prices.ProductId 
Prices.ProductId IS NULL 
AND
Prices.PriceType = 'W'

I have not tested yet..

hope this help to implement..

Upvotes: 1

Related Questions