join problems

[primerov]

I have a query that shows all the clients who have received an offer.It works excellent,
it shows even the clients who have turned down the offer and those who have not.
SELECT TblClients.ClientID, TblOffers.offerid
FROM TblClients INNER JOIN TblOffers ON TblClients.ClientID = TblOffers.Clientid;



I need now to introduce a third possibility.Namely, to enumerate those clients,who have not been aproached
and have not received an offer, i.e. those clients where Offerid = 0

However, my query does not show these clients.If a client has no offer,he is not shown on the query.
Can i join the tables in a way to show also the clients without an offer ?

 

[TheFitz]

Basically, from what you have written, I believe you want all from tblClients and those from tblOffers that match.

From the Access query designer, you should have a black line from TblClients.ClientID to TblOffers.Clientid.

If you right click this line and select Join Properties, then select the option that says all from TblClients and only those that match from TblOffers, you should get what you want.

I believe that the SQL generated should be as below:

SELECT TblClients.ClientID, TblOffers.offerid
FROM TblClients LEFT JOIN TblOffers ON TblClients.ClientID = TblOffers.Clientid;

Keep me informed, and let me know how it goes.

Hope that helps.

Fitz

 

[PHV]

Have you tried outer join ?
SELECT TblClients.ClientID, Nz(TblOffers.offerid, 0) As Offer
FROM TblClients LEFT JOIN TblOffers ON TblClients.ClientID = TblOffers.Clientid;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244