SQL Query grouping by a same ID

Question

Supppose I have a table called 'AnimalClass' that looks like this:

AnimalId	Gender	SocialID
1	male	X001
1	female	X2F8
1	none	XGW7
2	male	A902
2	female	A2F5
2	none	AGW6
3	male	T0R3
3	female	T2F0
3	none	TGW1

I am looking for a query that would return only 'male' and 'female' genders that share a same Animalid category where the Socialid matches 'X2F8' OR 'A2F5'

Basically the result should be only male and female genders that share a matching Animalid and the Socialid of the female matches 'X2F8' OR 'A2F5':

AnimalId	Gender	SocialID
1	male	X001
1	female	X2F8
2	male	A902
2	female	A2F5

I've made a few attempts but I just can wrap my head around it:

SELECT * FROM AnimalClass 
   WHERE (Gender = 'male' OR Gender = 'female')
   AND (SocialID = 'X2F8' OR SocialID = 'A2F5')

This query doesn't eventually give me results for both male and female but only for female.

Since SQL includes data definition, a [mcve] for an [SQL question](//meta.stackoverflow.com/q/333952/90527) should include [DDL](//en.wikipedia.org/wiki/Data_definition_language) statements for sample tables (rather than an ad hoc table specification) and [DML](//en.wikipedia.org/wiki/Data_manipulation_language) statements for sample data (rather than a dump or ad hoc format). Desired results don't need to be presented as sample code, as results are the output of code and not code themselves. — outis, Sep 24 '22 at 11:33
Please tag this question with the RDBMS you're using or, if it should work for any RDBMS, mention so in the question. — outis, Sep 24 '22 at 11:36
Don't use [`SELECT *`](/q/321299/90527) unless you're writing a DB administration program; select only the columns you need. — outis, Sep 24 '22 at 11:43

score 1 · Accepted Answer · edited Sep 24 '22 at 12:01

1

You can do it with EXISTS

SELECT a1.* 
FROM AnimalClass a1
WHERE (Gender = 'male' OR Gender = 'female')
  AND EXISTS (
    SELECT 1
    FROM AnimalClass a2
    WHERE (a2.SocialID = 'X2F8' OR a2.SocialID = 'A2F5')
       AND a2.AnimalId = a1.AnimalId
    )

edited Sep 24 '22 at 12:01

outis

75,655
22
151
221

answered Sep 23 '22 at 18:09

Serg

22,285
5
21
48

score 0 · Answer 2 · answered Sep 24 '22 at 11:46

Using joins, the requirements translate fairly directly into SQL.

the result should be only male and female genders

This translates to a Gender IN ('male', 'female') in the WHERE clause on the result rows.

that share a same Animalid category

This is where the join comes in, as you're comparing rows with each other. This also identifies the join column: AnimalId

the Socialid of the female matches 'X2F8' OR 'A2F5'

This translates to a Gender = 'female' AND SocialID IN ('X2F8', 'A2F5') in the WHERE clause on the comparison rows (i.e. not the result rows).

Since the conditions should all hold, they're combined with AND.

Put that all together, and you get:

SELECT results.AnimalId, results.Gender, results.SocialID
  FROM AnimalClass AS results
    JOIN AnimalClass AS filter ON results.AnimalId = filter.AnimalId
  WHERE results.Gender IN ('male', 'female')
    AND filter.Gender = 'female'
    AND filter.SocialID IN ('X2F8', 'A2F5')
;

SQL Query grouping by a same ID

2 Answers2