SQL: instances of column a where column b equals value 1 and 2-CodePudding

Basically I want the results to show any instance of column c where column h equals 'h1' and 'h2'.

I tried using the IN operator but it shows any instance of column c where column h is equal 'h1' or 'h2'

This is what I have so far:

select a, b, c, d, e, f, g, h, i
from table t
inner join table2 t2 on t.c = t2.c
inner join table3 t3 on t3.f = t2.f
inner join table4 t4 on t4.b = t.b
-- note: a,c,h are the columns in question, everything else is extra info
where e = e1
  and h in (h1, h2)
  and c not in (select c from table t where (h = h1 and i = i0))
order by h asc

and the results (with only relevant columns) are returned as:

a   c   h
----------
a1  c1  h1
a1  c1  h1
a2  c2  h2
a2  c2  h2
a3  c3  h1
a3  c3  h2

but I want it to get only this:

a   c   h
----------
a3  c3  h1
a3  c3  h2

EDIT: I shall simplify this and use an example and hopefully this makes more sense

Basically I want the results to show any instance of column Pet Shop where column Animal equals 'Cat' and 'Dog'.

I tried using the IN operator but it shows any instance of column Pet Shop where column Animal is equal 'Cat' or 'Dog'

This is what I have so far:

select * from table t
where Animal in (Cat, Dog)
order by Pet Shop asc

and the results are returned as:

Pet Shop     Animal
-------------------
Dogs Galore  Dog 1
Dogs Galore  Dog 2
Cats Galore  Cat 1
Cats Galore  Cat 2
Pet 'r' us   Dog 3
Pet 'r' us   Cat 3

but I want it to get only this:

Pet Shop     Animal
-------------------
Pet 'r' us   Dog 3
Pet 'r' us   Cat 3

as it is the only Pet Shop with both cats and dogs

CodePudding user response：

You have a result set, but you only want to show rows from it where for the pair of a and c there exists both a row with h = 'h1' and h = 'h2'. One way to do this is using conditional aggregation in an analytic function:

select a, b, c, d, e, f, g, h, i
from
(
  select
    a, b, c, d, e, f, g, h, i,
    case when
      count(case when h = 'h1' then 1 end) over (partition by a, c) > 0 and
      count(case when h = 'h2' then 1 end) over (partition by a, c) > 0
    then 'yes' else 'no' end as both_exist
  from table t
  inner join table2 t2 on t.c = t2.c
  inner join table3 t3 on t3.f = t2.f
  inner join table4 t4 on t4.b = t.b
  where e = 'e1'
    and h in ('h1', 'h2')
    and c not in (select c from table t where (h = 'h1' and i = 'i0'))
) checked
where both_exist = 'yes'
order by a, c, h;

(Disclaimer: I have not really examined your query, but took it as is. I just added the both_exists expression, which should be about what you need.)

CodePudding user response：

First We will be removing the duplicates using cte . Upon removing duplicates we will be joining with table having Count of h values greater than 2 .

;WITH CTE AS (
 SELECT DISTINCT * FROM sample)
SELECT S.*
from CTE S
INNER JOIN (SELECT a,c FROM CTE WHERE h in ('h1','h2') group by a,c having count(*)>=2)T1 ON T1.a= S.a AND T1.c =S.C;