Prev: Update Queries
Next: Query to add fake records from table
From: Damian on 22 Mar 2010 20:55
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
From: John W. Vinson on 22 Mar 2010 21:47
On Mon, 22 Mar 2010 17:55:01 -0700, Damian <Damian(a)discussions.microsoft.com>
wrote:

>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");

--

John W. Vinson [MVP]
From: Damian on 24 Mar 2010 01:44
I wouldn't have thought of doing that in 1000 years. Thank you very much John
Damian

"John W. Vinson" wrote:

> On Mon, 22 Mar 2010 17:55:01 -0700, Damian <Damian(a)discussions.microsoft.com>
> wrote:
>
> >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");
>
> --
>
> John W. Vinson [MVP]
> .
>
 | 
Pages: 1
Prev: Update Queries
Next: Query to add fake records from table