get Value from Table only if all rows match

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)

get Value from Table only if all rows match

Author

Topic

MacJK
Starting Member

24 Posts

Posted - 2011-11-18 : 07:51:08

Hello,

i have this Table:

O|A AS dbo.TestX
---
y|1
y|2
y|3
y|4
z|1
z|2
z|5
z|6
v|2
v|5

now i want to ask for same A in a where like

SELECT [O]
,[A]
FROM dbo.[TestX]
WHERE [O] IN ('y','z','v')

all i want to get is

A = 2 becouse it is in y,z,v at the same time.

if i ask WHERE [O] IN ('y','z') i want to get only

A = 1,2

is this posible with one query ot did i need a cursor. I work alread some time on this and canot get a way..

For all Help and Hint many thanks !!

br
Jaroslaw

br
Jaroslaw

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-18 : 08:00:42

select A
from tbl
where O in ('y','z','v')
group by A
having sum(case when O in ('y','z','v') = 3

select A
from tbl
group by A
having sum(case when O in ('y','z') = 2

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

MacJK
Starting Member

24 Posts

Posted - 2011-11-18 : 08:31:57

Hello nigelrivett,

thank you for the SQL Command.

But the problem is the condition some time inculde critery what not match. Like ('a','y','z','v') this mean there is no Value for 'a'.

br
Jaroslaw

br
Jaroslaw

MacJK
Starting Member

24 Posts

Posted - 2011-11-18 : 08:35:40

Hi, one more mey be my example is not good. I want to know without the WHERE wich [A] is pressent on all [O]?

i this table onle A = 2

br
Jaroslaw

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-18 : 08:45:47

Oops I left in the where clause in the top query and both were missing the end of the case statement.

select A
from tbl
group by A
having sum(case when O in ('y','z','v') then 1 else 0 end) = 3

select A
from tbl
group by A
having sum(case when O in ('y','z') then 1 else 0 end) = 2

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

MacJK
Starting Member

24 Posts

Posted - 2011-11-18 : 09:07:34

Hi this works close... i try this..

the point is like i descripe the table contains diffrent values on [O]

It should be like this

select A
from [dbo].[Table_X]
group by A
having sum(case when o in ((SELECT DISTINCT [o] FROM [dbo].[Table_X])) then 1 else 0 end) = SELECT COUNT(DISTINCT [o]) FROM [dbo].[Table_X]

but this dones't work. (Cannot perform an aggregate function on an expression containing an aggregate or a subquery.)

The Table i preform dinamic and is already a result.

How this work without the 'y','z','v'

But many thanks this alread move me forward ...

Best regards,
Jaroslaw

br
Jaroslaw

MacJK
Starting Member

24 Posts

Posted - 2011-11-18 : 09:14:46

Hi, got it thanky you!

select A, COUNT(A) As C
from [dbo].[Table_X]
group by A
having COUNT(A) = (SELECT COUNT(DISTINCT [o]) FROM [dbo].[Table_X])

Have a nive Weekend!

Best regards,
Jaroslaw

br
Jaroslaw

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources