Query

[nd0911]

Hello,

I have a sales table:

SalesId | LeadId | ProductId
------------------------------------------
1 | 1 | 1
-----------------------------------------
2 | 1 | 2
-----------------------------------------
3 | 2 | 1
-----------------------------------------
4 | 3 | 2
-----------------------------------------
5 | 2 | 2

I want to return a query that filter, lets say by productId = 1, that will return all leadId that dont have productId = 1


so the resulte of our table (if productId = 1) is:

leadId
-------
3
2

thank you !!

[ranman256]

make Q1 to pull all leads :
SELECT tData.LeadId, tData.ProductId FROM tData WHERE (((tData.ProductId)=1))

make Q2 an OUTER join to show the opposite:
in the query join Q1 to the data table, join on LeadID,
bring down LEADid from both tables into the grid,
dbl-click the join line
show ALL records in tData , and some in Q1
set criteria of: Q1.LeadId Is Null

this will pull all LeadIDs that did not have ProdID =1

SELECT tData.LeadId, Q1.LeadId
FROM Q1 RIGHT JOIN tData ON Q1.LeadId = tData.LeadId
WHERE (((Q1.LeadId) Is Null));

[ArviLaanemets]

SELECT DISTINCT LeadID FROM YourTable WHERE ProductID Not In (1)