Luc, This looks like you want a list of all Customers with Contacts (because you are basing it on the CONTACT_X_CUSTOMER table) and you want to show the Address (if it exists) or the Shipping Address (if it exists) instead of the Address? Am I close?
If I want to get one of two or more result choices in a column, I use an IF(), a CASE...END, an IFNULL(), a NULLIF() or a COALESCE() statement, depending on what it is I am choosing between. In your case I think you want to chose which address to use based on their existence, in this case, if it exists, it won't be null: SELECT x.ID_ADDRESS, c.customerName, COALESCE(s.ship_to_address, a.address, 'none') as address FROM Customer c INNER JOIN CONTACT_X_CUSTOMER x ON c.ID = x.CUSTOMER_ID LEFT JOIN Address a ON a.ID = x.ID_ADDRESS LEFT JOIN Address s ON s.ID = x.ID_ADDRESS_SHIPTO In this statement, the COALESCE statement will resolve to be the first non-null expression in the list. If there is no Address that matches your _X_ table's ID_ADDRESS_SHIP to then all of the columns in the table aliased as "s" will be NULL, Same with ID_ADDRESS and the table aliased as "a". If neither address exists the string 'none' is the result. HTH! Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Luc Foisy" <[EMAIL PROTECTED] To: "MYSQL-List (E-mail)" <[EMAIL PROTECTED]> -magic.com> cc: Fax to: 06/02/2004 10:46 AM Subject: JOINing complication, help please CONTACT_X_CUSTOMER.ID_ADDRESS CUSTOMER.ID_ADDRESS_SHIPTO CUSTOMER.ID_ADDRESS_MAIN What I would like is to be able to JOIN conditionally based on the absence/presence of reference SELECT ADDRESS.ID FROM CONTACT_X_CUSTOMER LEFT JOIN CUSTOMER ON CONTACT_X_CUSTOMER.ID_CUSTOMER = CUSTOMER.ID (thats the simple part, below is what I want, but it doesn't work of course, but the logic is kinda there) IF(CONTACT_X_CUSTOMER.ID_ADDRESS > 0, LEFT JOIN ADDRESS ON CUSTOMER_X_CONTACT.ID_ADDRESS = ADDRESS.ID, IF(CUSTOMER.ID_ADDRESS_SHIPTO > 0, LEFT JOIN ADDRESS ON CUSTOMER.ID_ADDRESS_SHIPTO = ADDRESS.ID, LEFT JOIN ADDRESS ON CUSTOMER.ID_ADDRESS_MAIN = ADDRESS.ID)) Sorry if I didn't break that up clear like, it was an attempt to make it more clear :) Is there ANY way I could get this to work (on the lastest version of the 3.x mysql) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]