Sorry to reply to myslef but I just saw my own typo. Here is a better
example statement:
SELECT x.ID_ADDRESS, c.customerName, COALESCE(s.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
(the problem was: the s and the a tables are aliases of the same table so
they should have had the same column names. SORRY !!!)
[EMAIL PROTECTED]
To: "Luc Foisy" <[EMAIL
PROTECTED]>
06/02/2004 11:31 cc: "MYSQL-List (E-mail)" <[EMAIL
PROTECTED]>
AM Fax to:
Subject: Re: JOINing complication,
help please
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
