The following SQL:

SELECT
 O.ID,
 O.FullName,
 A.FullAddress as MainAddress,
 IF(A1.FullAddress IS NULL THEN A.FullAddress ELSE A1.FullAddress) as DeliveryAddress
 FROM Organisations O
 LEFT JOIN Addresses A ON (O.ID=A.LinkID)
 LEFT OUTER JOIN Addresses A1 ON (O.ID=A1.LinkID)
WHERE O.ID = 7531
AND A.AddressType = 'Main Address'
AND A1.AddressType = 'Delivery Address'

returns the "fullname of an organisation, plus 2 address fields.

Not all Organisations have both types of address entered in the "Addresses" table, some only have a "main address".

My understanding is that the above should generate a ROW if Organisations has a ROW for the Organisations table with an ID Of 7531, even if there is no "Delivery Address" for that Organisation, i.e. the second call on the "Addresses" table might not return a row, but that should not affect things.

However, when I run the SQL I get no records returned unless the Organisation has BOTH a Main and Delivery Address.

I have tried replacing
 LEFT OUTER JOIN Addresses A1 ON (O.ID=A1.LinkID)
with
 RIGHT OUTER JOIN Addresses A1 ON (O.ID=A1.LinkID)
... and tried INNER to no effect.

How can I get the above SQL to return a row for the existing Organisation which does not have a Delivery Address?

Adam,

Please try this one instead :

SELECT
  O.ID,
  O.FullName,
  A.FullAddress as MainAddress,
  IF(A1.FullAddress IS NULL THEN A.FullAddress ELSE A1.FullAddress) as DeliveryAddress
FROM
  Organisations O
  LEFT OUTER JOIN Addresses A ON (O.ID=A.LinkID AND A.AddressType = 'Main Address' )
  LEFT OUTER JOIN Addresses A1 ON (O.ID=A1.LinkID AND A1.AddressType = 'Delivery Address')
WHERE
  O.ID = 7531

--
Fernando Dias
[Team Elevate]

>>Please try this one instead :

Arrgh ... d'oh!!!

Thanks Raul!

Thanks to Fernando in this case who is the wizard of SQL here!

Raul


<<Adam Brett wrote:

Thanks Raul!>>