Excluding records based on multiple criteria.

[AndyHorn]

How can I exclude a customer completely from a queries results if that customer meets 1 or more of the specified criteria? Let me explain:

I have a Customer table and a Mailshot table linked by Customer Number. There are about 25 different mailshots. A customer can have been sent any combination of these mailshots. I want my query to exclude customers that have been sent mailshots 1, 2 & 3, which is fine, however if a customer has been sent 1, 2 & 9 I want it to exclude that customer completely because they have had 1 & 2. The trouble I am having is that it is leaving the customer in my results with mailshot 9.

What can I do?

I hope this makes sense.

Thanks in advance.

 

[PHV]

Can you please post your actual SQL code ?
The usual way is an NOT EXISTS subquery where mailshot in (1,2,9) grouped by customer having count(*)=3

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[AndyHorn]

Here's my code. It's just a standard SELECT query at present, as I have no idea how to get the results I want.

SELECT CustomerMailshotList.[CustomerNumber], Mailshots.Mailshot
FROM Mailshots INNER JOIN CustomerMailshotList ON Mailshots.[CustomerNumber] = CustomerMailshotList.[CustomerNumber]
GROUP BY CustomerMailshotList.[CustomerNumber], Mailshots.Mailshot;

How does your example work if it could be any combination of mailshots? You've explicitly stated In(1,2,9)as per my example, but there any lots of possible combinations.

If a customer has ever had a mailshot from the 'Exclude' list below, even if he's had one from the Include list below, then they must be ommitted from the query results.
The query results should only include customers who have only ever had a mailshot from the 'Include' list.


Mailshots to Exclude
1,4,5,11-19

Mailshots to Include
2,3,6-10,20

Hope this makes more sense of what I want to achieve.

 

[PHV]

Something like this ?
SELECT C.CustomerNumber, M.Mailshot
FROM Mailshots M INNER JOIN CustomerMailshotList C ON M.CustomerNumber = C.CustomerNumber
WHERE M.Mailshot IN (2,3,6,7,8,9,10,20)
AND NOT EXISTS (SELECT * FROM Mailshot E WHERE E.CustomerNumber=C.CustomerNumber AND E.Mailshot IN (1,4,5,11,12,13,14,15,16,17,18,19))
GROUP BY C.CustomerNumber, M.Mailshot
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[AndyHorn]

Thanks for your help PHV,

I get an error message saying that it could not find the table or query 'Mailshot'. I think this is referring to the part of your expression ..AND NOT EXISTS (SELECT * FROM Mailshot E.........

I don't think it understands what E is. Is E the Exists part of the code?

Thanks again...

 

[PHV]

Sorry for the typo, replace this:
AND NOT EXISTS (SELECT * FROM Mailshot E
By this:
AND NOT EXISTS (SELECT * FROM Mailshots E

C, M and E are aliases.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[AndyHorn]

Getting there. It's now prompting for parameter values for E.CustomerNumber, C.CustomerNumber & M.CustomerNumber. Any ideas why?

Do I need to change the every instance of 'C.' to my actual table, 'CustomerMailshotList.' and 'M.' to Mailshots.Mailshot? What about 'E'?

 

[lespaul]

why don't you post the actual SQL you are trying to run when you get the parameter prompts?



Leslie

 

[AndyHorn]

It's the same as the code PHV posted except I've changed the In statements to include the actual mailshots in my db.

SELECT C.CustomerNumber, M.Mailshot
FROM Mailshots M INNER JOIN CustomerMailshotList C ON M.CustomerNumber = C.CustomerNumber
WHERE M.Mailshot IN ("1*","2*","5*","6*","7*","8*","9*","Order Card*")
AND NOT EXISTS (SELECT * FROM Mailshots E WHERE E.CustomerNumber=C.CustomerNumber AND E.Mailshot IN ("0*","3*","4*","FC*"))
GROUP BY C.CustomerNumber, M.Mailshot;

Thanks

 

[lespaul]

Well, you added the * does each Mailshot have 1*, 2* or are you trying to use a wild card? You have to use LIKE to use the wild card.

Leslie

 

[PHV]

Isn't CustomerNumber the real name of a field in both Mailshots and CustomerMailshotList tables ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[AndyHorn]

It does exactly the same whether I use 'IN (1,2,3,4,5)' or 'IN ("1*","2*","3*","4*","5*")', but yes I would like to use a wildcard once the code works.

It seems to me it's asking for parameter values because it doesn't know what 'E' is. There is no table for 'E'......'C' & 'M' are my tables so what is 'E'?

Thanks again..

 

[AndyHorn]

Yes it is PHV.

 

[PHV]

C, M and E are aliases defined for the duration of the query and are perfectly legal, even in old ac97.
It seems to me it's asking for parameter values because a spelling error with CustomerNumber.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

So if you paste this query and run it, you are prompted for all three customernumber fields?

SELECT C.CustomerNumber, M.Mailshot
FROM Mailshots M INNER JOIN CustomerMailshotList C ON M.CustomerNumber = C.CustomerNumber
WHERE M.Mailshot IN ("1","2","5","6","7","8","9","Order Card")
AND NOT EXISTS (SELECT * FROM Mailshots E WHERE E.CustomerNumber=M.CustomerNumber AND E.Mailshot IN ("0","3","4","FC"))
GROUP BY C.CustomerNumber, M.Mailshot;

Leslie

 

[AndyHorn]

Hi, thanks for all your help so far 'PHV' and 'lespaul'.

Basically the reason it wasn't working for me was because of a typo I made as 'PHV' suggested.....don't worry, your code works!!!!!

The actual Mailshot names are quite long that's why I wanted to use wildcards. From your previous post 'lespaul' I take it I can't use "1*" for it to include a Mailshot beginning with '1'? So would I have to replace the In statement with; Like "1*" or Like "2*" or Like "3*" etc. etc?

One last question. I've tried it and it seems to work but if I simply swapped the two 'In' statements should I get the opposite results?

Thanks again....I have a lot of SQL to learn

 

[lespaul]

yes you will need the Like "1*" or Like "2*"

If you swap the IN statements you will get the opposite results.

We all still have lots to learn! I learn something new everyday!

leslie