Query help

[Matthew]

I have two tables. I would like to create a query that selects everything
from [sub] and only those records from [contact] where the joined fields are
equal.
This works:

SELECT sub.*, contact.*, contact.email
FROM sub LEFT JOIN contact ON sub.ID = contact.sub

The trick I have is, I want to limit the records returned from contact:
WHERE (((contact.email)='somebody'));
However, I still want all the records from sub.

Any ideas how this could be done?

Matthew

 

[[MVP] S.Clark]

Create one query

Select * from contact WHERE (((contact.email)='somebody'))

Then use that query as the basis in your outer join query, instead of the
contacts table.

SubQuery or two queries, whichever you like better.

 

[John Vinson]

SELECT sub.*, contact.*, contact.email
FROM sub LEFT JOIN contact ON sub.ID = contact.sub

The trick I have is, I want to limit the records returned from contact:
WHERE (((contact.email)='somebody'));
However, I still want all the records from sub.

Add a criterion

OR Contact.ID IS NULL

to select records which do not match any record in Sub.


John W. Vinson[MVP]

 

[Matthew]

I like that idea. I gave it a shot, and got the error "syntax error in JOIN
operation"
SELECT *
FROM sub LEFT JOIN
(Select * from contact WHERE (((contact.email)='somebody')))
ON sub.ID = contact.sub

My guess is I shouldn't be calling that subquery "contact". However, I
don't know what it should be called.

Matthew

Create one query

Select * from contact WHERE (((contact.email)='somebody'))

Then use that query as the basis in your outer join query, instead of the
contacts table.

SubQuery or two queries, whichever you like better.


--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

I have two tables. I would like to create a query that selects everything
from [sub] and only those records from [contact] where the joined fields
are equal.
This works:

SELECT sub.*, contact.*, contact.email
FROM sub LEFT JOIN contact ON sub.ID = contact.sub

The trick I have is, I want to limit the records returned from contact:
WHERE (((contact.email)='somebody'));
However, I still want all the records from sub.

Any ideas how this could be done?

Matthew

 

[Matthew]

I got it!

SELECT *
FROM sub LEFT JOIN
(Select * from contact WHERE (((contact.email='somebody'))) ) AS sq
ON sub.ID=sq.sub

Thanks for your help.

Matthew

I like that idea. I gave it a shot, and got the error "syntax error in
JOIN operation"
SELECT *
FROM sub LEFT JOIN
(Select * from contact WHERE (((contact.email)='somebody')))
ON sub.ID = contact.sub

My guess is I shouldn't be calling that subquery "contact". However, I
don't know what it should be called.

Matthew

Create one query

Select * from contact WHERE (((contact.email)='somebody'))

Then use that query as the basis in your outer join query, instead of the
contacts table.

SubQuery or two queries, whichever you like better.


--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

I have two tables. I would like to create a query that selects
everything from [sub] and only those records from [contact] where the
joined fields are equal.
This works:

SELECT sub.*, contact.*, contact.email
FROM sub LEFT JOIN contact ON sub.ID = contact.sub

The trick I have is, I want to limit the records returned from contact:
WHERE (((contact.email)='somebody'));
However, I still want all the records from sub.

Any ideas how this could be done?

Matthew