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

Question

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.

What does Table C have to do with the question? Also, can you show the desired output you want? It's way better than a long text to explain. — Paul Karam, Feb 11 '18 at 06:42
Duplicte of https://stackoverflow.com/questions/15977126/select-rows-that-match-all-items-in-a-list — Salman A, Feb 11 '18 at 11:50

Kaushik Nayak · Accepted Answer · 2018-02-11T12:08:14.957

2

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 |

edited Feb 11 '18 at 12:08

answered Feb 11 '18 at 07:23

Kaushik Nayak

30,772
5
32
45

1

I don't think you need the CASE block at all: HAVING COUNT(distinct jid) = 2 works fine. – jwolf Feb 11 '18 at 07:45
Argh - I take it back. She wanted to test for J1 and J2 specifically. – jwolf Feb 11 '18 at 07:47
My answer works but yours is better. It's clever; a little faster than my self join (45 vs 55 in SQL Server - actually thought yours would win by more); and most importantly it can be easily extended to test more criteria. This answer should win. – jwolf Feb 11 '18 at 08:02
Filter J1 and J2 inside WHERE clause. – Salman A Feb 11 '18 at 11:51

score 1 · Answer 2 · edited Jun 20 '20 at 09:12

1

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.

edited Jun 20 '20 at 09:12

Community

1
1

answered Feb 11 '18 at 06:07

jwolf

908
7
13

1

It's better if you remove the first part of the answer and only keep the updated. It's no use to keep an old revision in the answer if it's false. – Paul Karam Feb 11 '18 at 06:51
@PaulKaram - I totally agree. At the time, I was keeping it as an "If you meant x, then this; if you meant y, then that" 2-part answer. I think the first part is moot at this point but I haven't removed it because... – jwolf Feb 11 '18 at 07:19
@PaulKaram - [cont] ...someone downvoted this answer (if it was you, no hard feelings at all because your point is legit but) it sure looked like it was the other guy who came up with this exact same answer (I saw his rep. drop a point when it happened) and if I had edited my answer again it will no longer be evident that mine was answered and edited first. Petty, huh? Can you tell it's my first downvote? What a way to spend a Sat. evening. Also, thanks for your comment on that other (3rd) answer. I was trying to politely nudge him in the right direction - a second voice is always good. – jwolf Feb 11 '18 at 07:28
I didn't downvote your answer. Your downvote could have been before you updated your answer, and the downvoter didn't see that you got it right. (Although I would edit it to be `Select Distinct a1.Cid` ;). – Paul Karam Feb 11 '18 at 07:29
@PaulKaram - Right again on the alias. The downvote happened a few minutes after I posted my update. I was actually in the middle of writing a nice great-minds-think-alike note to the other guy when it happened - that's why I noticed his rep. drop. Anyway I'm going to edit this thing and move on. Have a good one! – jwolf Feb 11 '18 at 07:37
1

@jwolf - I ignored your "did you downvote me comment" simply because I didn't want to be petty. However, I'm going to speak... looking at the timestamps and the revisions your first post missed the requirement. I saw your answer come up when I was drafting mine, you quickly realized and said you were "updating..." when I posted my answer you quickly updated yours to include my answer (it is true I had a typo in my ON statement that you corrected). Let's be frank, it was very coincidental that your answer included mine after it was posted but I let it go as a coincidence. – ThatTechGuy Feb 12 '18 at 00:07
@jwolf - I will add I did downvote you at the time because you were still referencing a misguided statement (and I felt a bit slighted) but I'll remove that now since you've corrected yours. – ThatTechGuy Feb 12 '18 at 00:08
@ThatTechGuy - This whole thing is embarrassing so I'm just going to say for the record: this is a dead simple self join; I didn't copy yours; and I didn't, for one second, dream of downvoting you when (from my end at least) the timeline was the other way around. I'm going to chalk up my over-sensitivity to me trying to end a long and frustratingly unproductive day yesterday on a high note by at least helping somebody else - and then having that go south too. So, sorry about that. Thank You for removing your downvote; that was nice of you to do. – jwolf Feb 12 '18 at 00:55

score 0 · Answer 3 · answered Feb 11 '18 at 06:07

0

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

answered Feb 11 '18 at 06:07

clocksp33d

44
5

This is not true at all. You can get the desired output of the OP. – Paul Karam Feb 11 '18 at 06:50

ThatTechGuy · Answer 4 · 2018-02-11T06:19:20.450

0

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

edited Feb 11 '18 at 06:19

answered Feb 11 '18 at 06:14

ThatTechGuy

879
1
10
29

This is a duplicate, but it is hard to tell who posted first. – Tom Aranda Feb 11 '18 at 20:25

nazark · Answer 5 · 2018-02-11T07:26:12.540

0

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

edited Feb 11 '18 at 07:26

answered Feb 11 '18 at 06:35

nazark

1,240
2
10
15

Wouldn't this find a Cid that, for instance, was paired with J1 twice but never paired with J2? – jwolf Feb 11 '18 at 06:42
@jwolf You're right, this doesn't give the right answer for the OP except for the sample data he gave. If any Cid was paired with J1 twice and never with J2 it will be given in the output. – Paul Karam Feb 11 '18 at 06:53

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

5 Answers5

Self Join Solution