sql query

[MSSQL123]

a b c d e f
---------------------------------------------
225 33 1 1 1 1006
225 33 2 1 1 196
226 33 13 1 1 1006
226 33 26 1 1 196
227 33 10 1 1 1006


can anyone give me the sql to display rows only when coloumn - f have both 1006 and 196


thanks

 

[AlexCuse]

select a, b, c, d, e, f
from table
where f = 1006 or f = 196

or, if you want to only display rows that have both values for one value in A (225 and 226 in example), this:

select a, b, c, d, e, f
from leTable t
inner join
(
	select a from
	(
		select a from leTable where f = 1006
	) x
	inner join 
	(
		select a from leTable where f = 196
	) z
	on x.a = z.a
) s
on t.a = s.a

You could also use IN for this, but I try to avoid use of IN when possible.

HOpe this helps,

Alex

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[PHV]

What about this ?
SELECT D.a, D.b, D.c, D.d, D.e, D.f
FROM yourTable D INNER JOIN (
SELECT a FROM yourTable WHERE f IN(1006,96) GROUP BY a HAVING COUNT(*)=2
) G ON D.a = G.a
WHERE D.f IN(1006,96)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886