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]

Reply via email to