Query displaying data from first table if no matches 1

[scriggs]

I have a table of clients with names and addresses.

I have a second table with alternate addresses.

The relationship is one client to many alterates. The tables are linked by a unique number field in the clients table.

I am trying to write a query which selects a client from clients table and shows the address from clients table. Easy.

If the client has an alternate address I want the query to show the address from the alternate table. I can get this to work when the client has alternate addresses, but if the client has none, then my query returns no records.

Any suggestions?

 

[Remou]

A different join type perhaps?
[tt]SELECT Address FROM Clients1 LEFT JOIN Clients2 ON Clients1.ID = Clients2.ID;[/tt]
If this works for you, you may like to look at some information on join types. If so, say so, and I will try and find it!

 

[scriggs]

thanks Remou

I am currently using a left join. My SQL is:
SELECT IB01.ClientCode, IB01.Address, Ib06.Address, Ib06.HNumber1
FROM IB01 LEFT JOIN Ib06 ON IB01.ClientNumber = Ib06.ClientNumber
WHERE (((IB01.ClientCode)="TLA01"));

If the client has no IB06 record I get no IB06 record which is fine

ClientCode IB01.Address Ib06.Address HNumber1
TLA01 Norton Mill

If the client has a n IB06 record I get the IB06 records which is fine

ClientCode IB01.Address Ib06.Address HNumber1
ABB02 Lower Heathcote Street Name 1
ABB02 Lower Heathcote Street Town 51
ABB02 Lower Heathcote High Street 2

BUT I need ABB02 to also show the first line with no Ib06 record related, and the 3 underneath.

This is for a mail merge query and I will call the query supplying a ClientCode and possibly a HNumber, planning to add 'AND ((Ib06.HNumber1)=51 Or (Ib06.HNumber1) Is Null));' to my SQL.

This doesn't work when I supply ClientCode=ABB02 and HNumber=Null as it doesn't return a record.

 

[Remou]

Sorry for getting you wrong. How about a Union query? I think it might work:
[tt]SELECT IB01.ClientCode, IB01.Address,"" As HNumber1
FROM IB01
UNION Select
IB06.ClientCode, IB06.Address, IB06.HNumber1
FROM IB06[/tt]

 

[scriggs]

Will give it a try. Couple of queries on unions, which I have used a bit in the past.

Will the union select bring in the 3 fields in the related table?
Do union selects run slowly, or OK?

 

[Remou]

I think the answer regarding speed is: it depends. Here is a document:

http://www.dbazine.com/ofinterest/oi-articles/celko2

Do you mean the three records? If so, yes.

You can also say:
[tt]SELECT IB01.ClientCode, IB01.Address,"" As HNumber1
FROM IB01 WHERE ClientCode="ABB02"
UNION Select
IB06.ClientCode, IB06.Address, IB06.HNumber1
FROM IB06 WHERE ClientCode="ABB02" AND HNumber1=51[/tt]

 

[scriggs]

Thanks Remou, just what I needed.