Remove all rows from group - 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 2005 Forums

Transact-SQL (2005)

Remove all rows from group

Author

Topic

jhaney
Starting Member

33 Posts

Posted - 2010-10-06 : 16:01:08

I need to remove all of the rows if any part of the group if any of the rows meet the requiremenst of the having clause? is this possible.

select group, test
from table
group by group
having test not like 'test3'

Example

Group1 test1
test2
test3
Group2 test1
test2
Group3 test1
test2
basically I want to return just groups 2 and 3

Group2 test1
test2
Group3 test1
test2

Hope this makes sense to someone. LOL

thanks

Joshua

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-06 : 16:21:05

If you can give a useful example...
Table structure, sample data and wanted output/result.
Then we can help...LOL

No, you're never too old to Yak'n'Roll if you're too young to die.

jhaney
Starting Member

33 Posts

Posted - 2010-10-07 : 09:06:50

Attached is the query, basically it returns to me all of my customers between the ages of 39-50. What I am looking to do now is show the customers that purchased from department1 but not from department2. the issue I am having is that if a customer purchased from department1 and department2 I do not want them on the list. So currently the having clause or a where clause not like Department2 does not help me.

SELECT clmaster.account, mwappts.customer, mwappts.department
FROM CLMASTER INNER JOIN
MWAPPTS ON CLMASTER.COMPANY = MWAPPTS.COMPANY AND CLMASTER.ACCOUNT = MWAPPTS.ACCOUNT
WHERE (CLMASTER.COMPANY = 'company')
AND (CLMASTER.DOB BETWEEN '1960-01-01 00:00:00.000' AND '1971-12-31 00:00:00.000')
and mwappts.purchasedate between '9/1/2010' and '9/30/2010'
group by clmaster.account, mwappts.customer, mwappts.department
order by clmaster.account

SO here is what I get now

Customer Dept
John 1
Mark 1
Tracy 2
Lisa 1
Lisa 2
Keri 1
Michell 1
Michell 2

and here is what i want

Customer Dept
John 1
Mark 1
Keri 1

I hope this makes sense

jhaney
Starting Member

33 Posts

Posted - 2010-10-11 : 13:53:46

Just bumping this to the top, anyone?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-11 : 14:15:54

Try this:

SELECT cm1.account, mw1.customer, mw1.department
FROM CLMASTER cm1
INNER JOIN MWAPPTS mw1
  ON cm1.COMPANY = mw1.COMPANY AND cm1.ACCOUNT = mw1.ACCOUNT
WHERE (cm1.COMPANY = 'company') 
AND (cm1.DOB BETWEEN '1960-01-01 00:00:00.000' AND '1971-12-31 00:00:00.000') 
and mw1.purchasedate between '9/1/2010' and '9/30/2010'
and mw1.department = 1
and not exists(select * from CLMASTER cm2
               INNER JOIN MWAPPTS mw2 
                 ON cm2.COMPANY = mw2.COMPANY AND cm2.ACCOUNT = mw2.ACCOUNT
               WHERE (cm2.COMPANY = cm1.COMPANY) 
               AND (cm2.DOB BETWEEN '1960-01-01 00:00:00.000' AND '1971-12-31 00:00:00.000') 
               and mw2.purchasedate between '9/1/2010' and '9/30/2010'
               and mw2.department = 2
               and cm2.ACCOUNT = cm1.ACCOUNT)
group by cm1.account, mw1.customer, mw1.department
order by cm1.account