Query Help - 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

Query Help

Author

Topic

Dev@nlkss

134 Posts

Posted - 2009-05-07 : 03:23:49

Hi
following is my sample data

ID MID Status
1 1 False
2 1 False
3 1 False
4 2 False
5 2 True
6 2 False

I have to pick only those MIDs where all Status of that MID should be False.
My output should be as

MID
---
1

ThankQ.

$atya.

Love All Serve All.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-07 : 03:30:57

[code]
DECLARE @TABLE TABLE
(
ID int,
MID int,
Status varchar(10)
)
INSERT INTO @TABLE
SELECT 1, 1, 'False' UNION ALL
SELECT 2, 1, 'False' UNION ALL
SELECT 3, 1, 'False' UNION ALL
SELECT 4, 2, 'False' UNION ALL
SELECT 5, 2, 'True' UNION ALL
SELECT 6, 2, 'False'

SELECT MID
FROM @TABLE
GROUP BY MID
HAVING COUNT(MID) = SUM(CASE WHEN Status = 'False' THEN 1 ELSE 0 END)

SELECT MID
FROM @TABLE
GROUP BY MID
HAVING COUNT(MID) = COUNT(CASE WHEN Status = 'False' THEN Status END)

SELECT MID
FROM @TABLE
GROUP BY MID
HAVING MAX(Status) = 'False'
[/code]

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

Dev@nlkss

134 Posts

Posted - 2009-05-07 : 03:37:45

thanks its working,
but Status is bit type.
where last query gives error.

$atya.

Love All Serve All.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-07 : 04:14:16

it is because my sample table definition for status is varchar. Just change it accordingly


SELECT	MID
FROM	@TABLE
GROUP BY MID
HAVING MAX(convert(int, Status)) = 0

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-07 : 05:04:43

quote:

Originally posted by khtan


DECLARE @TABLE TABLE
(
  ID  int,
  MID   int,
  Status  varchar(10)
)
INSERT INTO @TABLE
SELECT  1, 1, 'False' UNION ALL
SELECT  2, 1, 'False' UNION ALL
SELECT  3, 1, 'False' UNION ALL
SELECT  4, 2, 'False' UNION ALL
SELECT  5, 2, 'True'  UNION ALL
SELECT  6, 2, 'False'

SELECT  MID
FROM  @TABLE
GROUP BY MID
HAVING  COUNT(MID)  = SUM(CASE WHEN Status = 'False' THEN 1 ELSE 0 END)

SELECT  MID
FROM  @TABLE
GROUP BY MID
HAVING  COUNT(MID)  = COUNT(CASE WHEN Status = 'False' THEN Status END)

SELECT	MID
FROM	@TABLE
GROUP BY MID
HAVING MAX(Status) = 'False'

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

I dont prefer method 2 as it would give you

Warning: Null value is eliminated by an aggregate or other SET operation.

Madhivanan

Failing to plan is Planning to fail

mailsomani
Starting Member

4 Posts

Posted - 2009-05-07 : 05:27:32

Hi,

Let the table name be TEST with the following columns like ID,MID,STATUS.
If status is false then value is 0 else 1.
<Based on the above assumption please try below query>

select x.mid from (SELECT MID FROM TEST GROUP BY MID,STATUS )x
where x.mid in(select mid from test where status=0)
group by x.mid having count(x.mid)=1

Regards
Pawan Somani
WINWIRE TECHNOLOGIES

Pawansomani

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources