SELECT question 2

[rajkum]

Hi,

Table
----------------
Fld1 Fld2
----------------
JOHN 2
JACK 1
PETER 2
JOHN 13
---------------

How do I query to return all the Names in Fld1 where Fld2 is 2 AND 13.
I do not want to list PETER.

Thanks in advance.

 

[donutman]

Here's the method:

[Blue]SELECT[/Blue] Y1.Fld1 [Blue]FROM[/Blue] YourTable Y1 
   [Blue]INNER[/Blue] [Gray]JOIN[/Gray] YourTable Y2
      [Blue]ON[/Blue] Y1.Fld1[Gray]=[/Gray]Y2.Fld2 
   [Blue]WHERE[/Blue] Y1.Fld2[Gray]=[/Gray]2 [Gray]AND[/Gray] Y2.Fld2[Gray]=[/Gray]13

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[vongrunt]

You probably thought about this:

select Y1.Fld1
from YourTable Y1
inner join YourTable Y2 on Y1.fld1=Y2.[b]fld1[/b]
where Y1.fld2=2 and Y2.fld2=13

Another way... this one is lame:

select distinct A.Fld1
from YourTable A
where exists(select * from YourTable B where B.Fld1=A.fld1 and Fld2=2) 
and exists(select * from YourTable B where B.Fld1=A.fld1 and Fld2=13)

And another:

select Fld1
from
(	select Fld1, Fld2
	from YourTable
	where Fld2 in (2, 13)
	group by Fld1, Fld2
) X
group by Fld1
having count(*) = 2

 

[r937]

vongrunt, i can see you've been drinking the derived table koolaid again

it doesn't have to be that complex

select Fld1
  from YourTable
 where Fld2 in (2, 13)
group 
    by Fld1
having count(*) = 2

this technique is the only one that scales easily

select Fld1
  from YourTable
 where Fld2 in (2, 13, 937)
group 
    by Fld1
having count(*) = 3

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[vongrunt]

What if (Fld1, Fld2) aren't unique?

From original post this wasn't clear... so I played safe .

 

[rajkum]

Thnak you guys!

 

[dbomrrsm]

what if the data was

JOHN 2
JACK 1
PETER 2
JOHN 2

this would return john with the above solutions but it doesnt meet the original criteria of being 2 AND 13

[blue]DBomrrsm[/blue]

 

[dbomrrsm]

Select Fld1 from
(select distinct(Fld1+cast(Fld2 as varchar)) a,Fld1
from tab
where Fld2 in (2,13)
GROUP BY Fld1,Fld2) X
group by Fld1
having count (Fld1) = 2

[blue]DBomrrsm[/blue]

 

[vongrunt]

dbomrrsm, see my 3rd query. It does the same but without DISTINCT and CAST() stuff.

If (Fld1, Fld2) are always unique (no duplicates), rudy's method is enough. If not, it requires minor modification:

select Fld1
  from YourTable
 where Fld2 in (2, 13)
group 
    by Fld1
having count([b]distinct Fld2[/b]) = 2

... which is basically the same as 3rd query (even exec plans are likely to be identical).

 

[dbomrrsm]

didnt see that one Von

[blue]DBomrrsm[/blue]