SQL query problem - 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

General SQL Server Forums

New to SQL Server Programming

SQL query problem

Author

Topic

marekpracz
Starting Member

7 Posts

Posted - 2009-05-28 : 10:01:39

Hi,

Let say I've got table

Meeing_id,ticket_id
1 , 11
1 , 22
1 , 33
2 , 22
2 , 33
3 , 11
3 , 33
4 , 33

I want to select all meetings_id's that doesn't have ticket 11 but have 33.
What is best way to do that ?
If I use (where ticked_id <> 11) it will exclude only single row with that Id not all meeting_id's.

Thanks
Marco

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-28 : 10:03:11

[code]
select distinct meeting_id
from table t
where t.ticket_id = 33
and not exists
(
select *
from table x
where x.meeting_id = t.meeting_id
and x.ticket_id = 11
)
[/code]

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-28 : 10:12:34

or

select Meeting_id from table
group by Meeting_id
having max(case when ticket_id=33 then 1 when ticket_id=11 then 2 else 1 end)=1

Madhivanan

Failing to plan is Planning to fail

marekpracz
Starting Member

7 Posts

Posted - 2009-05-28 : 10:14:32

Work excellent

Thanks a lot !

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-28 : 10:20:19

My code can be simplified to

select Meeting_id from table
group by Meeting_id
having max(case when ticket_id=11 then 2 else 1 end)=1

Madhivanan

Failing to plan is Planning to fail

marekpracz
Starting Member

7 Posts

Posted - 2009-05-28 : 10:31:10

What if I would complicate thinks a bit and divide data into two tables

Meeting and Meeting_detail ?

So meeting table would contain meeting_id and table meeting_detail would contain both meeting_id and ticked_id and of course meeting_detail_id ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-28 : 11:27:45

extending Madhi's logic


SELECT m.*
FROM meeting m
INNER JOIN (SELECT meeting_id
            FROM meeting_detail
            GROUP BY meeting_id
            HAVING max(case when ticket_id=11 then 2 else 1 end)=1
           )md
ON md.meeting_id=m.meeting_id

marekpracz
Starting Member

7 Posts

Posted - 2009-05-29 : 04:15:42

visakh16 - thanks for SQl but it is not working as it shold.
It does show all meetings where ticket 11 is not in it - but there is another condition that it must have ticket 33. Instead above query display all even those meetings that have only ticket 22 !

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-29 : 04:44:51

[code]DECLARE @Sample TABLE
(
MeetingID INT,
TicketID INT
)

INSERT @Sample
SELECT 1, 11 UNION ALL
SELECT 1, 22 UNION ALL
SELECT 1, 33 UNION ALL
SELECT 2, 22 UNION ALL
SELECT 2, 33 UNION ALL
SELECT 3, 11 UNION ALL
SELECT 3, 33 UNION ALL
SELECT 5, 22 UNION ALL
SELECT 4, 33

SELECT MeetingID
FROM @Sample
GROUP BY MeetingID
HAVING MAX(CASE TicketID WHEN 11 THEN 2 WHEN 33 THEN 1 ELSE 0 END) = 1[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources