help with formulating 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)

help with formulating query

Author

Topic

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2010-04-06 : 08:57:26

I need help with a query, here, if you please.

The table looks something like this:


ID   Record    Category    Value   ...
1      100        A         25
2      100        B         50
3      100        C         0
4      100        D         100
5      200        A         100
6      200        D         50
7      200        E         75
...

For reach record, I want to compare input values to Category A, B, C, and D. In order to return a resulting set, the input values ALL have to match their respective counterparts. So, if I have inputs 25, 50, 100, and 100 for Categories A, B, C, and D, respectively, I won't see a result set for Record 100. So, using a where condition like ((category=A and value=@Ainput) or (category=B and value=@Binput) or ...) won't be of use because all the conditions have to be true. The problem is replacing the "or" with the "and" won't work because of the way the table is structured, and I have no control over the table structure.

So, I'm wondering how can I create an ad-hoc query that won't be a tax on performance that will query, for each record, only those results which match the values column in the table for each respective category column? I could use EXISTs but it can be very taxing, and I will need to combine this table with other tables and do processing on A LOT of Records.

So, I'm asking for any suggestions, any help at all.

Thank you!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-06 : 09:03:39

refer http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=142073

KH
[spoiler]Time is always against us[/spoiler]

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2010-04-06 : 10:44:59

Thank you. I am using SQL 2000; so, the WITH, partition, and OVER keywords are not applicable. There doesn't appear to be a method that can be done in an ad-hoc query for something like this, to my knowledge, with the exception of EXISTS, which could be a performance hit. Looks like it must be done through some t-sql.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-06 : 10:46:35

the basic technique is still the same.

KH
[spoiler]Time is always against us[/spoiler]

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-06 : 10:52:11

Can you supply a table containing the expected output for the query you require? I'm having difficulty understanding exactly what you're asking for.

There are 10 types of people in the world, those that understand binary, and those that don't.

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2010-04-06 : 11:07:06

Sure.

The Result set should simply be:


Record
------
100
200
300
500
700
1000
...

The query is returning matching records that meet all the criteria. The criteria is thus: I'm supplying input values from server-side code that must match the Value column in the table I prescribed, but it must also be of the same Category.

So, the query I tried looked something like this:

Select Record
From T_Cat
Where ((Category='A' and Value=@InputA) and (Category='B' and Value=@InputB) and (Category='C' and Value=@InputC) and (Category='D' and Value=@InputD)). This query wouldn't work, obviously, but if the table was formatted like this,


ID   Record    CategoryA    CategoryB     CategoryC    CategoryD     CategoryE
1      100        25         50              0            100
2      200        100                                     50            75  
...

then it would meet my requirements. But because the table is formatted like in the original post, I can't use this query. Likewise, this also won't meet my requirements:

Select Record
From T_Cat
Where ((Category='A' and Value=@InputA) or (Category='B' and Value=@InputB) or (Category='C' and Value=@InputC) or (Category='D' and Value=@InputD)). Why? Because this is an all or none condition. Either all the Inputs are match and the query returns the Record column value. If any of the inputs don't match, the query won't return that Record column value.

So, how can I check if the Record exists for all matching input values, not some, all?

Thank you very much.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-06 : 11:26:21

Does this help? NOTE: This assumes a unique constraint on Record, Category.


CREATE TABLE #tmp (
	ID INT,
	Record INT,
	Category VARCHAR(1),
	Value INT
)

INSERT INTO #tmp
SELECT 1,      100,        'A',        25
UNION ALL SELECT 2,      100,        'B',         50
UNION ALL SELECT 3,      100,        'C',         0
UNION ALL SELECT 4,      100,        'D',         100
UNION ALL SELECT 5,      200,        'A',         100
UNION ALL SELECT 6,      200,        'D',         50
UNION ALL SELECT 7,      200,        'E',         75

SELECT Record,  SUM(CASE WHEN Category = 'A' THEN Value ELSE NULL END) AS A,
				SUM(CASE WHEN Category = 'B' THEN Value ELSE NULL END) AS B,
				SUM(CASE WHEN Category = 'C' THEN Value ELSE NULL END) AS C,
				SUM(CASE WHEN Category = 'D' THEN Value ELSE NULL END) AS D,
				SUM(CASE WHEN Category = 'E' THEN Value ELSE NULL END) AS E
FROM #tmp
GROUP BY Record

DROP TABLE #tmp

There are 10 types of people in the world, those that understand binary, and those that don't.

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2010-04-06 : 12:03:05

Shoot! I think that would work! I just realized that some of the values in the Value column may also have characters in them. So, the SUM function won't be able to handle varchar data. But this is really good! I guess what I have been asking for is a way to pivot the table in SQL, without modifying the table. That probably sums up my request.

Thank you for your help, by the way.

Update: Nevermind, I just replaced SUM with MAX, so as to be able to select varchar values. It works. Thank you very much. This is exactly what was I looking for! Essentially Pivot the table.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-06 : 12:30:30

Yep, it's a SQL 2000 pivot, without the PIVOT Command. In 2005, you can PIVOT without having to do it manually like this. But in 2000, I think this sort of method is the best way of doing it.

Yes, MAX will work also.

There are 10 types of people in the world, those that understand binary, and those that don't.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources