need help with formulating a complex query

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)

need help with formulating a complex query

Author

Topic

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2009-08-10 : 11:43:58

I really don't know how to phrase this problem in a few short words, which is why the Subject line is ambiguous.

I have a table, right. It has ColumnNum and ColumnType, amongst other fields. ColumnType may have a value of A or B. So, it'd look something like this:


ColumnNum             ColumnType
5                     A
50                    B
100                   A
100                   B
101                   A
101                   B
450                   A
459                   A
459                   B
...

What I want to do is select all ColumnNums that have a value of A but do NOT have a value of A and B! So, it'd look something like this:


ColumnNum             ColumnType
5                     A
450                   A
...

That is, any ColumnNum that has records with ColumnType of A and B are filtered out. I'm having difficulty formulating the query, because it's not a matter of checking if ColumnType has A or B in one record but multiple records for the same ColumnNum.

I would appreciate any help. Thank you in advance.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-10 : 11:49:37

try this one
select t.columnnum,t.columntype from tablename t
left join tablename s on s.columnnum = t.columnnum and s.columntype ='b'
where t.columntype = 'a' and s.columnnum is null

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-08-10 : 11:52:45

maybe this...

select * from <table> t
where not exists(select * from <table> t1 where t1.ColumnNum = t.ColumnNum and t1.ColumnType = 'B')

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2009-08-10 : 12:15:35

Hey, thank you both for responding.

I tried the second one and it didn't work. I tried the first suggestion, as well. I think it's correct! I'm pretty sure I got the results I wanted, but it looks to be correct. I have to analyze the query, but I'd surely appreciate your throught process, as to how you came up with that query!!! In any event, I am grateful for the help!

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-08-10 : 12:32:39

If you have an index on (ColumnNum, ColumnType) then also try the following:

SELECT ColumnNum, 'A' AS ColumnType
FROM YourTable
GROUP BY ColumnNum
HAVING COUNT(CASE WHEN ColumnType = 'A' THEN '1' END) > 0
    AND COUNT(CASE WHEN ColumnType = 'B' THEN '1' END) = 0

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-11 : 00:39:25

quote:
Originally posted by SQLIsTheDevil

Hey, thank you both for responding.

I tried the second one and it didn't work. I tried the first suggestion, as well. I think it's correct! I'm pretty sure I got the results I wanted, but it looks to be correct. I have to analyze the query, but I'd surely appreciate your throught process, as to how you came up with that query!!! In any event, I am grateful for the help!

welcome

i am getting the values of the a from t and b from s and comparing the columnnum with t and s tables (used left join and is null in where condition) which gives the unequal values

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources