Query to find matching records in 2 tables

[kbp]

Hi there i'm not sure that the title to this question is correct, but basically i've got 2 tables, that have very similar data in.



an [enrolment] table which holds information about clients and where they enrolled. A client could have enrolled on a course from 1 of 7 different sites.

and an [outcomes] table which holds information about the clients after the course.

i have created a query to filter out records from the [enrolment] table so i only see the records from a specific site.

i have created another query to display records from the [outcomes] to only view certain records.

now i want to cross-refference the 2 queries to only display records that occur in both queries, and a seperate query that displays records that dont occur in both queries.

anyone know how to do this??

cheers
Andy

[ajetrumpet]

you can use the IN subquery. for instance, syntax:

select whatever from table where field (NOT) IN (SELECT whatever from othertable)

if your records have unique identifiers to them, it's even better because the subquery only has to take one field in it as an arg.

[weekend00]

records that occur in both queries:
select * from query1 inner join query2 on query1.id=query2.id

records that occur in query1 not occur in query2:
select * from query1 left join query2 on query1.id=query2.id where query2.id is null

[ajetrumpet]

you can still inner join datasets if they are not related?

[weekend00]

they are related by clients

[orange]

How about showing us the Table structures?
And what specifically you want the query/queries to do/show?

[kbp]

hiya guys thanks for the tips, been out of the office most of the day and haven't had a chance to try your ideas... They all look good though and i can see where your going with them!! I'll give them a go when i get a chance and let you know.

cheers