Query issue - 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 2008 Forums

Transact-SQL (2008)

Query issue

Author

Topic

mugen2005
Starting Member

16 Posts

Posted - 2011-08-01 : 20:07:51

Hey Guys,
I'm going to try and explain this as best as possible.

Col1 Col2
aaa
aaa
aaa 222
bbb 333
ccc 555
ccc
ddd

what im trying to query is all rows which are blank in column 2 and not repeated in column 1. Heres what I had, using the count function
--------
select col1, count (*) as counter
from table
having count (*) <= 1
group by col1
--------
those results would look like this
bbb 1
ddd 1
which is close to what im looking for, however i still have col2 that I need to remove which would leave me with Row DDD. So I added a where clause

select col1, count (*) as counter
from table
where col2 =''
having count (*) <= 1
group by col1

By doing this I actually return more results instead of the intended DDD row. Basically it looks like its preforming the Where clause first and then doing the count.

Any ideas would be very helpful. Thank you

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-01 : 21:00:37

[code]
select *
from table t
inner join
(
select col1
from table
group by col1
having count(*) = 1
) a on t.col1 = a.col1
where col2 = ''
[/code]

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-01 : 21:02:29

Would this work for you?

SELECT
	col1
FROM
	[table] t1
WHERE
	NOT EXISTS( SELECT * FROM [table] t2 WHERE t2.col1=t1.col1 AND t2.col2='')
GROUP BY
	col2
HAVING
	COUNT(*) = 1;

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-01 : 21:06:19

Use the query KH posted. My query is trying to find the exact opposite of what you asked for. I would have tried to correct it, but-->

mugen2005
Starting Member

16 Posts

Posted - 2011-08-01 : 21:31:42

thanks for the info guys, I will try this out tomorrow and let you know. Thanks so much for the info

mugen2005
Starting Member

16 Posts

Posted - 2011-08-02 : 12:03:58

Worked like a champ, thanks again!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources