Stuck on query design - 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)

Stuck on query design

Author

Topic

benricho
Yak Posting Veteran

84 Posts

Posted - 2006-01-12 : 19:52:06

OK, a bit of background on the current design of the database:

Subscribers
-----------
SubscriberID int (primary key)
ListID int (foreign key)

SubscriberFieldData
-------------------
DataID int (primary key)
FieldID int (foreign key)
SubscriberID int (foreign key)
OptionID int (foreign key)

Basically, the db stores subscribers, who are grouped together in a list. This list can have custom fields (stored in another table), and the data for these fields is stored in the above table. Here's an example of what the data would look like:

DataID, FieldID, SubscriberID, OptionID
1, 55, 606, 12
2, 67, 606, 44
3, 55, 607, 12
4, 58, 608, 33

Now, I want to do a query that goes something like this: Give me all the SubscriberID's that have an OptionID of 12 for a FieldID of 55 and don't have an OptionID of 36 for a FieldID of 67 (but they must have a fieldID of 67 with another OptionID).

From the above query, I should get 606 as my only result.

I've been able to get this query working using a something like this:

SELECT s.SubscriberID FROM
Subscribers s
WHERE
s.ListID = 123 AND
(s.SubscriberID IN (SELECT DISTINCT sfd.SubscriberID FROM SubscriberFieldData sfd WHERE FieldID = 55 AND OptionID = 12)) AND
((s.SubscriberID IN (SELECT DISTINCT sfd.SubscriberID FROM SubscriberFieldData sfd WHERE FieldID = 67 AND OptionID != 36)) AND
(s.SubscriberID IN (SELECT DISTINCT sfd.SubscriberID FROM SubscriberFieldData sfd WHERE FieldID = 67 AND OptionID IS NOT NULL)))

But this is obviously really slow once I start working with decent amounts of data. Any ideas for a better solution that's going to play well with a heap of data?

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-12 : 20:34:15

Instead of Sub queries, use Join to improve speed.

But I don't know how this works for any fieldid value
eg.

(s.SubscriberID IN (SELECT DISTINCT sfd.SubscriberID FROM SubscriberFieldData sfd WHERE FieldID = 55 AND OptionID = 12))
AND
((s.SubscriberID IN (SELECT DISTINCT sfd.SubscriberID FROM SubscriberFieldData sfd WHERE FieldID = 67 AND OptionID != 36))
AND
(s.SubscriberID IN (SELECT DISTINCT sfd.SubscriberID FROM SubscriberFieldData sfd WHERE FieldID = 67 AND OptionID IS NOT NULL)))

fieldid = 55, 2nd & 3rd criteria won't be met
fieldid = 67, 1st criteria won't be met
fieldid = any other , all 3 criterias fail
Making the where claues always false!!!

May be I'm not seeing something.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-12 : 20:43:40

>> (but they must have a fieldID of 606 with another OptionID).
Is it FieldID of 606 or SubscriberID of 606 ?
Try this :

select 	d.*
from	Subscribers s inner join SubscriberFieldData d
	on	s.SubscriberID	= d.SubscriberID
where	(d.FieldID = 55 and d.OptionID = 12)
and	(
	not	(d.FieldID = 67 and d.OptionID = 36)
	and	(d.SubscriberID = 606 and d.OptionID is not null)
	)

-----------------
'KH'
Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.

benricho
Yak Posting Veteran

84 Posts

Posted - 2006-01-12 : 21:10:44

Sorry khtan, that should be "(but they must have a fieldID of 67 with another OptionID)".

Just wanted to quickly fix that before I look at your query.

benricho
Yak Posting Veteran

84 Posts

Posted - 2006-01-12 : 21:25:14

Srinika - I should have pointed out that a subscriber can have multiple SubscriberFieldData rows, like subscriber 606 in my sample data.

So subscriber 606 has to have a row where the fieldID is 55 and the OptionID is 12, and another row where the fieldID is 67 and the optionID isn't 36 (and in the sample data it's 44).

And I think this is why khtan's example doesn't work (unless I'm doing something wrong).

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-12 : 21:47:03

can you post more sample data and the expected result ?

-----------------
'KH'
if you can't beat them, have someone else to beat them

benricho
Yak Posting Veteran

84 Posts

Posted - 2006-01-12 : 22:09:59

No problem khtan, here's another set of data:

DataID, FieldID, SubscriberID, OptionID
5, 55, 610, 12
6, 67, 610, 44
7, 55, 611, 12
8, 67, 611, 44
9, 55, 612, 25
10, 67, 612, 44

I would like to be able to create a query that gives me all subcribers that have:
- an OptionID of 12 when the FieldID is 55
(and)
- an OptionID of 44 when the fieldID is 67

So the results from this should be subscriber 610 and 611. Subscriber 612 doesn't match because they have an OptionID of 25 for FieldID 55, when 12 is required.

Let me know if this isn't clear.

benricho
Yak Posting Veteran

84 Posts

Posted - 2006-01-13 : 17:41:47

Any more ideas on this problem?

benricho
Yak Posting Veteran

84 Posts

Posted - 2006-01-16 : 00:03:08

Instead of the IN I'm using an inner join which is much faster (thanks for the tip Srinika), but I'd still luck to improve the performance if there is a better way to get the same results. For the example above, I'm using a query like this:

SELECT COUNT(DISTINCT s.SubscriberID) FROM
ActiveSubscribers s WITH (NOLOCK)
INNER JOIN (SELECT DISTINCT sfd.SubscriberID FROM SubscriberFieldData sfd WHERE FieldID = 55 AND OptionID = 12) s1 ON s.SubscriberID = s1.SubscriberID
INNER JOIN (SELECT DISTINCT sfd.SubscriberID FROM SubscriberFieldData sfd WHERE FieldID = 67 AND OptionID = 44) s2 ON s.SubscriberID = s2.SubscriberID
WHERE
s.ListID = 123

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-16 : 11:36:51

I'm not sure about the following, but give it a try


SELECT COUNT(DISTINCT s.SubscriberID) FROM 
ActiveSubscribers s WITH (NOLOCK) 
INNER JOIN sfd.SubscriberID FROM SubscriberFieldData s1
ON s.SubscriberID = s1.SubscriberID and 
 ( (FieldID = 55 AND OptionID = 12)  or 
   (FieldID = 67 AND OptionID = 44) )
WHERE
s.ListID = 123

benricho
Yak Posting Veteran

84 Posts

Posted - 2006-01-16 : 20:42:56

Thanks for the answer Srinika, but I don't thins it's going to work because of the OR. Remember, the results I'm looking for are:

- an OptionID of 12 when the FieldID is 55
(and)
- an OptionID of 44 when the fieldID is 67

So both of those have to match. With this query, all the results are returned when only 1 of the criteria matches.

I'm still hoping there is a way that I don't have to do multiple inner joins for each extra rule like my current query?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2006-01-18 : 20:11:12

I think you can do it with an inline table (or derived view).

for example:

SELECT DISTINCT(s.SubscriberID)
FROM
(
SELECT SubscriberID
FROM SubscriberFieldData
WHERE FieldID = 55
AND OptionID = 12
) s
INNER JOIN
(
SELECT SubscriberID
FROM SubscriberFieldData
WHERE FieldID = 67
AND OptionID <> 12
) ss
ON s.SubscriberID = ss.SubscriberID

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2006-01-18 : 20:42:29

I realize that my previous query did not take everything into account, but I hoped that it was enough to get you to a solution.

Also, I was thinking about it and I think you could (depending on youe schema and data set) gain a little performance by using an IN rather than an INNER JOIN and DISTINCT (or GROUP BY). For example:

SELECT s.SubscriberID
FROM SubscriberFieldData s
WHERE
(
s.FieldID = 55
AND s.OptionID = 12
)
AND
SubscriberID IN
(
SELECT SubscriberID
FROM SubscriberFieldData
WHERE FieldID = 67
AND OptionID <> 12
)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources