Tricky SQL query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Tricky SQL query

Author

Topic

sphair
Starting Member

6 Posts

Posted - 2005-03-31 : 03:02:17

I have a dataset which looks somewhat like:

TREATMENTID_|_PRODUCTID_|_CUSTOMERID
T118________|___________|_10
T11_________|___________|_10
T46_________|___________|_10
____________|_P211______|_10
T36_________|___________|_10
____________|_P17_______|_10
T90_________|___________|_10
____________|_P102______|_10
T91_________|___________|_11

I need a SQL query on this table that returns all customers that have bought SOME of the treatments, SOME of the products, and did NOT buy SOME of the treatments and SOME of the products.

Example: I need a query to return all customers who bought ((treatment T118 or T11) AND (product P211)) AND ((not treatment T36 or T46) AND (not product P17)).

I'm using MSSql, without stored procedures as they queries are built dynamically each time.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-31 : 07:43:29

Here is the idea of one way to do this:


Select customerID
From	myTable a
left join ( --derived table of customers to exclude
	Select distinct customerID
	From myTable where <EXCLUDE conditions>
	) as exclude
		ON a.customerid = exclude.customerid
where	exclude.customerid is NULL
and	<INCLUDE condutions>

Be One with the Optimizer
TG

sphair
Starting Member

6 Posts

Posted - 2005-03-31 : 07:49:08

Thanks, haven't tried it out yet, but it looks like it might solve the exclude part.

But I still haven't figured out how I can get the both include statements to work. Where the customers need at least one of the treatments and at least one of the products.

Any ideas on that?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-31 : 08:02:00

Wouldn't this work?

Both include and exclude statments would be structured the same (just with different values) ie:
where TreatmentID IN('T118', 'T11') AND ProductID = 'T212'

Be One with the Optimizer
TG

sphair
Starting Member

6 Posts

Posted - 2005-03-31 : 08:08:55

No, it wouldn't that assume each row should contain both the treatment and the product?
No row will contain both items.
So it will either fail of not finding the product or failing by not finding the treatment.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-31 : 08:26:30

ahh...I see. this is a poor table design. These derived table need to "fake" the right structure.
How about this? (by the way, if you post the ddl and data I could test this on my side)


Select	t.customerID
From	(
	SElect distinct CustomerID
	From	myTable
	Where	TreatmentID IN('T118', 'T11') 
	) t
JOIN	(
	SElect distinct CustomerID
	From	myTable
	Where	ProductID = 'P211'
	) p
		ON t.customerid = p.customerID

Left JOIN
	(
	Select	t.customerID
	From	(
		SElect distinct CustomerID
		From	myTable
		Where	TreatmentID IN('T36', 'T46') 
		) t
	JOIN	(
		SElect distinct CustomerID
		From	myTable
		Where	ProductID = 'P17'
		) p
		ON t.customerid = p.customerID
	) exclude
		ON t.customerid = exclude.customerid
where	exclude.customerid is null

Be One with the Optimizer
TG

sphair
Starting Member

6 Posts

Posted - 2005-03-31 : 09:07:17

Thank you, this looks very good, but when I tested it it included a customer which had bought both the include and exclude treatments. So looks like the exclude section doesn't quite work?

Its hard for me to send a test database, as its a few Gbs large, and the example I've given is a shortened version. To demonstrate here is the query I had to use:

SELECT t.KUNDEID
FROM (
SELECT DISTINCT BESOK.KUNDEID
FROM SALG INNER JOIN BESOK ON SALG.BESOKID=BESOK.BESOKID
INNER JOIN BEHANDLING ON SALG.BEHANDLINGID=BEHANDLING.BEHANDLINGID
WHERE BESOK.SITE = 'B241' AND (BEHANDLING.BEHANDLINGID in ('B24146','B24185'))
) t
JOIN (
SELECT DISTINCT BESOK.KUNDEID
FROM SALG INNER JOIN BESOK ON SALG.BESOKID=BESOK.BESOKID
INNER JOIN VARE ON SALG.VAREID=VARE.VAREID
WHERE BESOK.SITE = 'B241' AND (SALG.VAREID IN ('B2411211'))
) p ON t.kundeid=p.kundeid

LEFT JOIN (

SELECT t.KUNDEID
FROM (
SELECT DISTINCT BESOK.KUNDEID
FROM SALG INNER JOIN BESOK ON SALG.BESOKID=BESOK.BESOKID
INNER JOIN BEHANDLING ON SALG.BEHANDLINGID=BEHANDLING.BEHANDLINGID
WHERE BESOK.SITE = 'B241' AND (BEHANDLING.BEHANDLINGID in ('B241110'))
) t
JOIN (
SELECT DISTINCT BESOK.KUNDEID
FROM SALG INNER JOIN BESOK ON SALG.BESOKID=BESOK.BESOKID
INNER JOIN VARE ON SALG.VAREID=VARE.VAREID
WHERE BESOK.SITE = 'B241' AND (SALG.VAREID IN ('B241381'))
) p ON t.kundeid=p.kundeid
) exclude ON t.kundeid = exclude.kundeid
where exclude.kundeid is null

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-31 : 09:44:33

quote:
Originally posted by sphair

No, it wouldn't that assume each row should contain both the treatment and the product?
No row will contain both items.
So it will either fail of not finding the product or failing by not finding the treatment.

These should really be separate tables, then. If there is no relation other than CustomerID between these values, then they should be each in separate transaction tables.

This also probably means your table has no primary key, right?

- Jeff

sphair
Starting Member

6 Posts

Posted - 2005-03-31 : 09:52:02

I have no chance to change the table layouts, as they are provided to me.

The tables are basically:
1. Visit table (besok)
2. Sales table (salg)

One visit can have many sales.

The visit row contains the reference to the customerid (kundeid).
Each sales row can be either a product sale or a treatment sale.

Each of the tables has a primary key. Visit (Besok) has BESOKID, Sales (Salg) has SALGID, etc.

Chester
Starting Member

27 Posts

Posted - 2005-03-31 : 10:03:23

Will this work for you?

Select customerID
From myTable
where treatment in ('t118','t11') and product = 'p211'
and 1 = (case when treatment = 't36' then 0 else 1 end)
and 1 = (case when treatment = 't46' then 0 else 1 end)
and 1 = (case when product = 'p17' then 0 else 1 end)

sphair
Starting Member

6 Posts

Posted - 2005-03-31 : 10:10:09

No, it wouldn't work, I'm afraid. For one, it wouldnt match the includes as noted in my post 03/31/2005 : 08:08:55. Excluding wouldnt work either, because I need to make sure it has exclude at least one of the products and at least one of the treatments.

Chester
Starting Member

27 Posts

Posted - 2005-03-31 : 10:23:50

Okay, how about this:

Select t.customerID
From (
SElect distinct CustomerID
From myTable
Where TreatmentID IN('T118', 'T11')
) t
JOIN (
SElect distinct CustomerID
From myTable
Where ProductID = 'P211'
) p
ON t.customerid = p.customerID
Where 1 = (case when (treatment = 't36' or treatment = 't46') then 0 else 1 end)
and 1 = (case when product = 'p17' then 0 else 1 end)

rfrancisco
Yak Posting Veteran

95 Posts

Posted - 2005-04-01 : 11:24:36

Try this one:

SELECT CustomerID
FROM YourTable A
WHERE EXISTS (SELECT 'X' FROM YourTable B
WHERE A.CustomerID = B.CustomerID AND
B.TreatmentID IN (<List of Treatments>)) AND
EXISTS (SELECT 'X' FROM YourTable D
WHERE A.CustomerID = D.CustomerID AND
D.ProductID IN (<List of Products>)) AND
EXISTS (SELECT 'X' FROM YourTable C
WHERE A.CustomerID = C.CustomerID AND
C.TreatmentID NOT IN (<List of Treatments>)) AND
EXISTS (SELECT 'X' FROM YourTable E
WHERE A.CustomerID = E.CustomerID AND
E.ProductID NOT IN (<List of Products>))

rfrancisco
Yak Posting Veteran

95 Posts

Posted - 2005-04-01 : 11:26:14

I think this one will do it:

SELECT CustomerID
FROM TableA A
WHERE EXISTS (SELECT 'X' FROM TableA B
WHERE A.CustomerID = B.CustomerID AND
B.TreatmentID IN (<List of Treatments>)) AND
EXISTS (SELECT 'X' FROM Table A D
WHERE A.CustomerID = D.CustomerID AND
D.ProductID IN (<List of Products>)) AND
NOT EXISTS (SELECT 'X' FROM TableA C
WHERE A.CustomerID = C.CustomerID AND
C.TreatmentID IN (<List of Treatments>)) AND
NOT EXISTS (SELECT 'X' FROM TableA E
WHERE A.CustomerID = E.CustomerID AND
E.ProductID IN (<List of Products>))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources