Find matching records based on two fields

[Damian]

Hi, I'd be really grateful if someone could please assist with my query. I'm
trying to identify all records where [Source] is equal to 'Atlas' that has
matching records in [Species] where [Source] is equal to 'Museum' OR 'Survey'
as per example below...

Original Table:

UniqueID Source Species
1 Atlas A
2 Atlas B
3 Atlas B
4 Atlas C
5 Atlas D
6 Survey B
7 Survey C
8 Museum C
9 Museum D

Query result:

UniqueID Source Species
2 Atlas B
3 Atlas B
4 Atlas C
5 Atlas D

Cheers, Damian

 

[John W. Vinson]

Hi, I'd be really grateful if someone could please assist with my query. I'm
trying to identify all records where [Source] is equal to 'Atlas' that has
matching records in [Species] where [Source] is equal to 'Museum' OR 'Survey'
as per example below...

Original Table:

UniqueID Source Species
1 Atlas A
2 Atlas B
3 Atlas B
4 Atlas C
5 Atlas D
6 Survey B
7 Survey C
8 Museum C
9 Museum D

Query result:

UniqueID Source Species
2 Atlas B
3 Atlas B
4 Atlas C
5 Atlas D

Cheers, Damian

A self join query will work here:

SELECT A.UniqueID, A.Source, A.Species
FROM yourtable AS A
INNER JOIN yourtable AS B
ON A.Species = B.Species
WHERE A.Source = "Atlas"
AND B.Source IN ("Museum", "Survey");

 

[Damian]

I wouldn't have thought of doing that in 1000 years. Thank you very much John
Damian

Hi, I'd be really grateful if someone could please assist with my query. I'm
trying to identify all records where [Source] is equal to 'Atlas' that has
matching records in [Species] where [Source] is equal to 'Museum' OR 'Survey'
as per example below...

Original Table:

UniqueID Source Species
1 Atlas A
2 Atlas B
3 Atlas B
4 Atlas C
5 Atlas D
6 Survey B
7 Survey C
8 Museum C
9 Museum D

Query result:

UniqueID Source Species
2 Atlas B
3 Atlas B
4 Atlas C
5 Atlas D

Cheers, Damian

A self join query will work here:

SELECT A.UniqueID, A.Source, A.Species
FROM yourtable AS A
INNER JOIN yourtable AS B
ON A.Species = B.Species
WHERE A.Source = "Atlas"
AND B.Source IN ("Museum", "Survey");