Help with Query

[WarcraftPlayer]

From the following table.

ID ColA ColB
11 55 FL
11 56 CA
11 898 FL
11 54654 YY
11 565 FL
44 1356 FL
44 2 CA
44 5655 XX

How would I get the following result
ID ColB
11 FL
11 YY

I want to get all the IDs that have both a FL and a YY in column B but not return IDs that have one or the other.

I have tried multiple ways but have not been able to isolate just the ones that have both FLs and YYs

/cry
/help


LVL 60 ROGUE

 

[SQLSister]

select id, colb from table1 t
join
(select id from
(select distinct id, 'fl' from table1 where colb = 'fl') a
join
(select distinct id, 'yy' from table1 where colb = 'yy') b
on a.id = b.id) c
on t.id = c.id
where colb in ('yy', 'fl')

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[cLFlaVA]

try

select id, colB
  from mytable
 where id in (select id from mytable where colB = 'FL')
   and id in (select id from mytable where colB = 'YY')

*cLFlaVA
----------------------------
[tt]( <P> <B>)[sup]13[/sup] * (<P> <.</B>)[/tt]

[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[gmmastros]

There are several ways, here's one.

[green]-- Dummy data for testing purposes[/green]
Declare @Temp Table(Id Integer, ColA Integer, ColB VarChar(10))

Insert Into @Temp Values(11,55      ,'FL')
Insert Into @Temp Values(11,56      ,'CA')
Insert Into @Temp Values(11,898     ,'FL')
Insert Into @Temp Values(11,54654   ,'YY')
Insert Into @Temp Values(11,565     ,'FL')
Insert Into @Temp Values(44,1356    ,'FL')
Insert Into @Temp Values(44,2       ,'CA')
Insert Into @Temp Values(44,5655    ,'XX')

[green]-- Query start here[/green]
Select A.Id,
       B.ColB
From   (
       Select Distinct A.Id
       From   @Temp A
              Inner Join @Temp B
                On A.Id = B.Id
       Where  A.ColB = 'FL'
              And B.ColB = 'YY'
      ) As A
      Cross Join (Select 'FL' As ColB Union All Select 'YY') As B

In your final query, replace @Temp with your actual table name.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[r937]

the general approach to this type of problem is to recognize that the situation you're looking for spans a number of rows, i.e. a "group of rows", and this should immediately suggest using GROUP BY ID

first filter for the rows you want (with a WHERE clause), then count how many you got --

select ID
  from daTable
 where ColB in ( 'FL','YY' )
group
    by ID
having count(*) = 2

if the pair of columns {ID,CoLB} is not unique, then you might need to use

having count(distinct ColB) = 2

guys, what would your solutions look like if you had to find all ids that have a FL row, a YY row, a CA row, and a TT row?

select ID
  from daTable
 where ColB in ( 'FL','YY','CA','TT' )
group
    by ID
having count(*) = 4

you can do all kinds of nice things in the HAVING clause, such as "FL, YY, and either CA or TT" --

select ID
  from daTable
 where ColB in ( 'FL','YY','CA','TT' )
group
    by ID
having sum(case when ColB in ('FL','YY') 
                               then 1 end) = 2
   and sum(case when ColB in ('CA','TT') 
                               then 1 end) > 0

using joins in these more complicated situations would surely trigger a nervous breakdown!



r937.com | rudy.ca