CODEWITHSUNDEEP
sqlpostgresql
NIYATI ACHARYA
NIYATI ACHARYA

Reputation: 73

Sql query to only get records that contain given values in a particular column

I have following tables:

Table A

Aid, Cid, Jid
A1,  C1,  J1
A2,  C1,  J2
A3,  C2,  J1
A4,  C2,  J3

Table C[Cid, X] with Cid as foreign key in table A.

I want to get all the CIds from table A that contains both the Jids J1 and J2. For above table, I want to return C1 as the output. If I put an AND clause, I don't get any records and if I put an OR clause for the J column, I also get C2 in the output. Please assist.

Upvotes: 1

Views: 90

Answers (5)

Kaushik Nayak
Kaushik Nayak

Reputation: 31656

You may either use a HAVING COUNT ( DISTINCT) with a CASE block or, as suggested by Salman, filter it in where clause.

SQL Fiddle

PostgreSQL 9.6 Schema Setup:

CREATE TABLE TableA
    (Aid varchar(3), Cid varchar(3), Jid varchar(3))
;

INSERT INTO TableA
    (Aid, Cid, Jid)
VALUES
    ('A1', 'C1', 'J1'),
    ('A2', 'C1', 'J2'),
    ('A3', 'C2', 'J1'),
    ('A4', 'C2', 'J3'),
    ('A5', 'C2', 'J3'),
    ('A7', 'C3', 'J2'),
    ('A8', 'C3', 'J2'),
    ('A9', 'C4', 'J1'),
    ('A10','C4', 'J1'),
    ('A11','C1', 'J3'),
    ('A12','C1', 'J2')

;

Query 1:

SELECT cid
FROM   TABLEA
GROUP  BY cid
HAVING COUNT (DISTINCT CASE jid
                         WHEN 'J1' THEN 'J1'
                         WHEN 'J2' THEN 'J2'
                       END) = 2

Results:

| cid |
|-----|
|  C1 |

Query 2:

SELECT cid
  FROM   TABLEA
  WHERE jid IN ('J1', 'J2')
    GROUP  BY cid
 HAVING COUNT ( DISTINCT jid ) = 2

Results:

| cid |
|-----|
|  C1 |

Upvotes: 2

jwolf
jwolf

Reputation: 938

Self Join Solution

This self join will get you the necessary combinations:

select distinct a1.Cid
    from A as a1
    inner join A as a2 on a2.Cid = a1.Cid
    where a1.Jid = 'J1' and a2.Jid = 'J2'

note: It only works with testing for 2 Jid's.

I hope this helps.

Upvotes: 1

nazark
nazark

Reputation: 1240

try,

    select distinct cid from TableA  a  outer  apply 
 (select jid from TableA where jid='J2' and cid=a.Cid) b 
where a.jid='J1' and  b.jid is not null

Upvotes: 0

ThatTechGuy
ThatTechGuy

Reputation: 889

SELECT DISTINCT Cid
FROM Table_A a
JOIN Table_A b
ON a.Cid = b.Cid
WHERE a.Jid = 'J1'
AND b.Jid = 'J2';

Upvotes: 0

clocksp33d
clocksp33d

Reputation: 44

get all the CIds from table A that contains both the Jids J1 and J2

This means that the query result will contain C1 and C2 based on data in Table A, so the below won't happen.

I want to return C1 as the output

Upvotes: 0

Related Questions