0

I have a table like this (this is simplified)

ID FOO BAR
1 100 200
1 101 202
1 102 205
2 100 200
2 101 222
2 102 203
3 100 201
3 101 202
3 102 204
4 100 201
4 101 202
4 102 205

If i query FOO = 100 and BAR = 200 the IDs returned are 1 and 2 that's all fine.

I would like to be able to do is FOO = 100 and BAR = 200 and then FOO=101 and BAR = 202 so i get returned only ID = 1

and if i did FOO = 100 and BAR = 201 and FOO = 101 AND BAR = 202 and FOO = 102 and BAR = 205 i would only be returned ID 4

if i did FOO = 100 and BAR = 201 the i would see the IDs 3 and 4

I think i should be able to do this with a recursive _CTE similar to Recurisve query in SQL Server but i cant quite get me head around how to structure it.

The levels that FOO can go too are not limited to 3 its just been simplified for this example

Anonymous
  • 835
  • 1
  • 5
  • 21
Gushie
  • 13
  • 4

2 Answers2

0

No need for recursive CTE. FOR FOO = 100 and BAR = 200 and then FOO=101 and BAR = 202, you can do this.

with cte
as
(

SELECT ID,FOO,BAR
FROM TAB
WHERE FOO = 100 and BAR = 200
)
SELECT ID
FROM cte
WHERE FOO=101 and BAR = 202

For FOO = 100 and BAR = 201 and FOO = 101 AND BAR = 202 and FOO = 102 and BAR = 205, you can do this:

with cte
    as
    (

    SELECT ID,FOO,BAR
    FROM TAB
    WHERE FOO = 100 and BAR = 201
    )
    SELECT ID 
    FROM
    (SELECT ID,FOO,BAR
    FROM cte
    WHERE FOO=101 and BAR = 202
    ) result(ID,FOO,BAR)
    WHERE FOO=102 and BAR = 205
Akshey Bhat
  • 8,227
  • 1
  • 20
  • 20
  • If i structure that query as 
    ;with _cte as
(
 SELECT ID
 FROM TAB
 WHERE foo = 100 AND BAR = 200
)
select * from _CTE
join TAB on TAB.id = _cte.id
WHERE TAB.foo = 101 AND TAB .bar=202
    it does get me part way there but i need to be able to apply a tertiary filter to the result e.g. if FOO=102 and BAR=205     – Gushie Jun 30 '17 at 10:31
  • You can use a nested query for that. See updated answer – Akshey Bhat Jun 30 '17 at 10:45
0

If I correctly understand, you need common ID's for every condition.

If so, and if FOO, BAR are unique for per ID, then you can do this:

with the_table(ID, FOO, BAR) as(
select  1 , 100 , 200 union all
select   1 , 101 , 202 union all
select   1 , 102 , 205 union all
select   2 , 100 , 200 union all
select   2 , 101 , 222 union all
select   2 , 102 , 203 union all
select   3 , 100 , 201 union all
select   3 , 101 , 202 union all
select   3 , 102 , 204 union all
select   4 , 100 , 201 union all
select   4 , 101 , 202 union all
select   4 , 102 , 205
)


select id from the_table
where
(FOO = 100 and BAR = 201) or
(FOO = 101 AND BAR = 202) or
(FOO = 102 and BAR = 205)
group by id
having count(*) = 3

Note that here: count(*) = 3, number 3 is count of conditions in where clause, for example if you have just one condition (FOO = 100 and BAR = 201), then query is:

select id from the_table
where
(FOO = 100 and BAR = 201)
group by id
having count(*) = 1
Oto Shavadze
  • 40,603
  • 55
  • 152
  • 236
  • Thanks this will do what i need, i wint know how many 'or' queries i will have but u will just build the query dynamically. – Gushie Jun 30 '17 at 11:45