GROUPING only if EXISTS

Question

GROUPING only if EXISTS

Dyamond

Grasshopper

Points: 23
More actions
January 31, 2009 at 4:55 pm

#196682

Hello, I'm trying to keep together/group on the following:
SELECT date,name,desc,‘Code’ as cdDesc
FROM activity
WHERE date = ‘2009-01-05′ and site = ‘80′ and
code in (’55544′,’33333′, ‘66666′)
SELECT date,name,desc,‘Code’ as cdDesc
FROM activity
WHERE date = ‘2009-01-05′ and site = ‘80′ and
code in (’55544′,’33333′, ‘66666′) and
EXISTS
(SELECT x.clasf_cd
FROM code
WHERE code in (’55544′,’33333′, ‘66666′)
I ONLY want to see ALL the results for the codes above as a group. The results come back empty if I use the AND condition. Also, EXISTS doesn't work becuase it brings back to many rows. Any help would be appreciated.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004683 More actions · Answer 1

Dyamond (1/31/2009)
Hello, I'm trying to keep together/group on the following:
SELECT date,name,desc,‘Code’ as cdDesc
FROM activity
WHERE date = ‘2009-01-05' and site = ‘80' and
code in (’55544',’33333', ‘66666')

I'm a bit thick on your description... are you saying you only want returns only if you have all 3 codes for the given date and site?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dyamond Grasshopper Points: 23 More actions · Answer 2

Dyamond

Grasshopper

Points: 23

January 31, 2009 at 5:29 pm

#936054

Yes.

Jeff Moden SSC Guru Points: 1004683 More actions · Answer 3

This is untested because you provided no table structure nor any readily consumable data in the form of INSERT statements... for future posts, please see the link in my signature. It will help us help you.

;WITH cteCount AS

(

SELECT Date, Site, COUNT(*) AS Cnt

FROM dbo.Activity

WHERE Date = '2009-01-05'

AND Site = 80

AND Code IN ('55544','33333','666666')

GROUP BY Date, Site

)

SELECT a.Date, a.Name, a.Desc, 'Code' AS cdDesc

FROM dbo.Activity a

INNER JOIN cteCount c

ON a.Date = c.Date

AND a.Site = c.Site

WHERE a.Date = '2009-01-05'

AND a.Site = 80

AND a.Code IN ('55544','33333','666666')

AND c.Cnt = 3

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dyamond Grasshopper Points: 23 More actions · Answer 4

Jeff,

Thanks for responding, but it still isn't working.

Table

Date, Name, Desc, Code

1/05/09, Test 1, This is Test 1, 55544

1/05/09, Test 1, This is Test 2, 33333

1/05/09, Test 1, This is Test 3, 666666

1/05/09, Test 1, This is Test 4, 888889

1/05/09, Test 2, This is Test 3, 666666

1/05/09, Test 2, This is Test 4, 888889

1/05/09, Test 3, This is Test 1, 55544

1/05/09, Test 3, This is Test 2, 33333

1/05/09, Test 3, This is Test 3, 666666

1/05/09, Test 4, This is Test 3, 666666

Expected End Result

1/05/09, Test 1, This is Test 1, 55544

1/05/09, Test 1, This is Test 2, 33333

1/05/09, Test 1, This is Test 3, 666666

1/05/09, Test 3, This is Test 1, 55544

1/05/09, Test 3, This is Test 2, 33333

1/05/09, Test 3, This is Test 3, 666666

1/05/09, Test 1, This is Test 4, 888889

I'm still getting ALL codes(55544, 33333, 666666) .

Your help is really appreciated.

Jeff Moden SSC Guru Points: 1004683 More actions · Answer 5

Dyamond (2/1/2009)
Jeff,
Thanks for responding, but it still isn't working.
Table
Date, Name, Desc, Code
1/05/09, Test 1, This is Test 1, 55544
1/05/09, Test 1, This is Test 2, 33333
1/05/09, Test 1, This is Test 3, 666666
1/05/09, Test 1, This is Test 4, 888889
1/05/09, Test 2, This is Test 3, 666666
1/05/09, Test 2, This is Test 4, 888889
1/05/09, Test 3, This is Test 1, 55544
1/05/09, Test 3, This is Test 2, 33333
1/05/09, Test 3, This is Test 3, 666666
1/05/09, Test 4, This is Test 3, 666666
Expected End Result
1/05/09, Test 1, This is Test 1, 55544
1/05/09, Test 1, This is Test 2, 33333
1/05/09, Test 1, This is Test 3, 666666
1/05/09, Test 3, This is Test 1, 55544
1/05/09, Test 3, This is Test 2, 33333
1/05/09, Test 3, This is Test 3, 666666
1/05/09, Test 1, This is Test 4, 888889
I'm still getting ALL codes(55544, 33333, 666666) .
Your help is really appreciated.

I asked you to put the data in the form of viable insert statements before. Please create a table creation statement and convert the example data to insert statements so I don't have to. And, since you're new on the forum, you really need to read the article in my signature.

Also, why is 888889 in there? Are the rules actually you want to return all rows for a given "Name" even if their codes are not in the criteria but only if they have all 3 codes?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)