Need help with select statement....

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

Transact-SQL (2000)

Need help with select statement....

Author

Topic

ggfox
Starting Member

4 Posts

Posted - 2004-07-01 : 22:57:38

Hello,

I'm trying to firgure out how to do the following selection efficiently;

Having a table of items, with an ID field and 5 category fields:

ITEMS
--------
ItemID   int
Cat0     char(1)
Cat1     char(1)
Cat2     char(1)
Cat3     char(1)
Cat4     char(1)

Each category field can be either blank ('') or have a category specified (say, 'A' ... through ... 'Z') in any particular order. For example:

ItemID Cat0 Cat1 Cat2 Cat3 Cat4
------ ---- ---- ---- ---- ----
1      G    C         F    
2      A    D    C         H
3      B    A         C

Now, I need to be able to select the records that contain at least two or more (up to 5) matching categories (@Cat0 ... @Cat4).

For example, all records with matching categories of 'A' and 'C' would return ItemIDs: 2 and 3.

I tried building the criteria using 'IN' lists, but the end result is an 'OR' selection rather than 'AND', due to the 'blanks' being allowed to match. Thus for the example criteria above ('A' and 'C') I get ItemIDs: 1, 2, and 3 instead of the required items 2 and 3.


SELECT ItemID
FROM ITEMS
WHERE
  (Cat0 IN ('',@Cat0,@Cat1,@Cat2,@Cat3,@Cat4)) AND
  (Cat1 IN ('',@Cat0,@Cat1,@Cat2,@Cat3,@Cat4)) AND
  (Cat2 IN ('',@Cat0,@Cat1,@Cat2,@Cat3,@Cat4)) AND
  (Cat3 IN ('',@Cat0,@Cat1,@Cat2,@Cat3,@Cat4)) AND
  (Cat4 IN ('',@Cat0,@Cat1,@Cat2,@Cat3,@Cat4))

Any help with this appreciated!
TIA,
George

ps: I can't rework the table structure (other than add more fields to it) due to existing constraints I'm not including here.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-02 : 00:07:18

What if you combined all of the fields and then checked to see how many match your criteria...

Declare @criteria table (filter char(1))
Insert Into @criteria Values ('A')
Insert Into @criteria Values ('C')

SELECT ItemID
FROM ITEMS
WHERE (Select count(*) From @criteria where (ITEMS.Cat0 + ITEMS.Cat1 + ITEMS.Cat2 + ITEMS.Cat3 + ITEMS.Cat4) like '%' + filter + '%')=(Select count(*) From @criteria)

May need a little clean up

Corey

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-07-02 : 04:20:11

would this work for you:

SELECT ItemID
FROM ITEMS
WHERE (cat0 = @cat0 or @cat0 is null)
and (cat1 = @cat1 or @cat1 is null)
and (cat2 = @cat2 or @cat2 is null)
and (cat3 = @cat3 or @cat3 is null)
and (cat4 = @cat4 or @cat4 is null)

the ones you don't search are null.

Go with the flow & have fun! Else fight the flow :)

ggfox
Starting Member

4 Posts

Posted - 2004-07-02 : 09:31:46

quote:
Originally posted by spirit1

would this work for you:

SELECT ItemID
FROM ITEMS
WHERE (cat0 = @cat0 or @cat0 is null)
and (cat1 = @cat1 or @cat1 is null)
and (cat2 = @cat2 or @cat2 is null)
and (cat3 = @cat3 or @cat3 is null)
and (cat4 = @cat4 or @cat4 is null)
...

Hi spirit1,

It would not work... The problem is that the categories are not stored in any particular order in the fields, thus if I query for records that have both 'A' AND 'C', I must be able to search for 'A' and 'C' in all the category fields (Cat0...Cat4) since they can be anywhere.

That is what I was trying to acomplish w/ the "IN" lists, but hit a road block when trying to accomodate the blank '' fields, since they will return a positive match as long as only one of the matches ('A' or 'C') are made and there are blank category fields in the record.

Thanks,
George.

ggfox
Starting Member

4 Posts

Posted - 2004-07-02 : 09:53:37

quote:
Originally posted by Seventhnight

...

Declare @criteria table (filter char(1))
Insert Into @criteria Values ('A')
Insert Into @criteria Values ('C')

SELECT ItemID
FROM ITEMS
WHERE (Select count(*) From @criteria where (ITEMS.Cat0 + ITEMS.Cat1 + ITEMS.Cat2 + ITEMS.Cat3 + ITEMS.Cat4) like '%' + filter + '%')=(Select count(*) From @criteria)

...

Hi Corey,

Thanks for the reply. Your solution seems to work so far (I need to test a few more variations of the problem).

But the only thing that concerns me about it is the performance. The simplified problem I descrived above is based on a table that has a couple million records and the query needs to be as efficient and fast as posible. I'm wondering how the nested SELECTs and the 'LIKE' will perform under that scenario and with the other criteria that will go in play. Hmm... It will probably be a non issue, since the select counts are done on the memory table. I'll run some tests later today on the real tables to see how things go.

Thanks!
George

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-02 : 10:21:53

I don't know if any indexes would help you in this case, but when I am working on something, I like to try any possible solutions. Hope it works out for you!!

Corey

ggfox
Starting Member

4 Posts

Posted - 2004-07-02 : 11:42:36

Ahh...

I think I found a way to the problem. It seems so obvious now (duh!)

just had to reverse my thinking a bit.

SELECT ItemID
FROM ITEMS
WHERE 
  (@Cat0 IS NULL OR @Cat0 IN (Cat0,Cat1,Cat2,Cat3,Cat4)) AND
  (@Cat1 IS NULL OR @Cat1 IN (Cat0,Cat1,Cat2,Cat3,Cat4)) AND
  (@Cat2 IS NULL OR @Cat2 IN (Cat0,Cat1,Cat2,Cat3,Cat4)) AND
  (@Cat3 IS NULL OR @Cat3 IN (Cat0,Cat1,Cat2,Cat3,Cat4)) AND
  (@Cat4 IS NULL OR @Cat4 IN (Cat0,Cat1,Cat2,Cat3,Cat4))

Thanks Corey & spriti1 for helping out!
George.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources