As horible as it looks, this is what I came out with...
SELECT CONTACT_X_CUSTOMER.ID, CONTACT.LastName AS 'Last Name', CONTACT.FirstName AS
'First Name', CONTACT_X_CUSTOMER.Email AS 'Email', CONTACT_X_CUSTOMER.Active AS
'Active', CONCAT(IF(CONTACT_X_CUSTOMER.ID_ADDRESS > 0,
CONTACT_X_CUSTOMER_ADDRESS.AddrLine1, IF(CUSTOMER.ID_ADDRESS_SHIPTO > 0,
SHIPTO_ADDRESS.AddrLine1, MAIN_ADDRESS.AddrLine1)), IF(CONTACT_X_CUSTOMER.ID_ADDRESS >
0, IF(CONTACT_X_CUSTOMER_ADDRESS.AddrLine2 IS NULL OR
TRIM(CONTACT_X_CUSTOMER_ADDRESS.AddrLine2) = '', '', CONCAT(' ',
CONTACT_X_CUSTOMER_ADDRESS.AddrLine2)), IF(CUSTOMER.ID_ADDRESS_SHIPTO > 0,
IF(SHIPTO_ADDRESS.AddrLine2 IS NULL OR TRIM(SHIPTO_ADDRESS.AddrLine2) = '', '',
CONCAT(' ', SHIPTO_ADDRESS.AddrLine2)), IF(MAIN_ADDRESS.AddrLine2 IS NULL OR
TRIM(MAIN_ADDRESS.AddrLine2) = '', '', CONCAT(' ', MAIN_ADDRESS.AddrLine2)))),
IF(CONTACT_X_CUSTOMER.ID_ADDRESS > 0, IF(CONTACT_X_CUSTOMER_ADDRESS.AddrLine3 IS NULL
OR TRIM(CONTACT_X_CUSTOMER_ADDRESS.AddrLine3) = '', '', CONCAT(' ',
CONTACT_X_CUSTOMER_ADDRESS.AddrLine3)), IF(CUSTOMER.ID_ADDRESS_SHIPTO > 0,
IF(SHIPTO_ADDRESS.AddrLine3 IS NULL OR TRIM(SHIPTO_ADDRESS.AddrLine3) = '', '',
CONCAT(' ', SHIPTO_ADDRESS.AddrLine3)), IF(MAIN_ADDRESS.AddrLine3 IS NULL OR
TRIM(MAIN_ADDRESS.AddrLine3) = '', '', CONCAT(' ', MAIN_ADDRESS.AddrLine3)))),
IF(CONTACT_X_CUSTOMER.ID_ADDRESS > 0, IF(CONTACT_X_CUSTOMER_CITY.Name IS NULL OR
TRIM(CONTACT_X_CUSTOMER_CITY.Name) = '', '', CONCAT(' ',
CONTACT_X_CUSTOMER_CITY.Name)), IF(CUSTOMER.ID_ADDRESS_SHIPTO > 0, IF(SHIPTO_CITY.Name
IS NULL OR TRIM(SHIPTO_CITY.Name) = '', '', CONCAT(' ', SHIPTO_CITY.Name)),
IF(MAIN_CITY.Name IS NULL OR TRIM(MAIN_CITY.Name) = '', '', CONCAT(' ',
MAIN_CITY.Name)))), IF(CONTACT_X_CUSTOMER.ID_ADDRESS > 0,
IF(CONTACT_X_CUSTOMER_PROVINCE.Name IS NULL OR TRIM(CONTACT_X_CUSTOMER_PROVINCE.Name)
= '', '', CONCAT(' ', CONTACT_X_CUSTOMER_PROVINCE.Name)),
IF(CUSTOMER.ID_ADDRESS_SHIPTO > 0, IF(SHIPTO_PROVINCE.Name IS NULL OR
TRIM(SHIPTO_PROVINCE.Name) = '', '', CONCAT(' ', SHIPTO_PROVINCE.Name)),
IF(MAIN_PROVINCE.Name IS NULL OR TRIM(MAIN_PROVINCE.Name) = '', '', CONCAT(' ',
MAIN_PROVINCE.Name)))), IF(CONTACT_X_CUSTOMER.ID_ADDRESS > 0,
IF(CONTACT_X_CUSTOMER_COUNTRY.Name IS NULL OR TRIM(CONTACT_X_CUSTOMER_COUNTRY.Name) =
'', '', CONCAT(' ', CONTACT_X_CUSTOMER_COUNTRY.Name)), IF(CUSTOMER.ID_ADDRESS_SHIPTO >
0,
IF(SHIPTO_COUNTRY.Name IS NULL OR TRIM(SHIPTO_COUNTRY.Name) = '', '', CONCAT(' ',
SHIPTO_COUNTRY.Name)), IF(MAIN_COUNTRY.Name IS NULL OR TRIM(MAIN_COUNTRY.Name) = '',
'', CONCAT(' ', MAIN_COUNTRY.Name)))), IF(CONTACT_X_CUSTOMER.ID_ADDRESS > 0,
IF(CONTACT_X_CUSTOMER_ADDRESS.PostalCode IS NULL OR
TRIM(CONTACT_X_CUSTOMER_ADDRESS.PostalCode) = '', '', CONCAT(' ',
CONTACT_X_CUSTOMER_ADDRESS.PostalCode)), IF(CUSTOMER.ID_ADDRESS_SHIPTO > 0,
IF(SHIPTO_ADDRESS.PostalCode IS NULL OR TRIM(SHIPTO_ADDRESS.PostalCode) = '', '',
CONCAT(' ', SHIPTO_ADDRESS.PostalCode)), IF(MAIN_ADDRESS.PostalCode IS NULL OR
TRIM(MAIN_ADDRESS.PostalCode) = '', '', CONCAT(' ', MAIN_ADDRESS.PostalCode))))) AS
'Shipping Address', IFCONTACT_X_CUSTOMER.ID_ADDRESS > 0, 'Contact Address',
IF(CUSTOMER.ID_ADDRESS_SHIPTO > 0, 'Customer Ship To', 'Customer Main')) AS 'Address
Type' FROM CONTACT_X_CUSTOMER LEFT JOIN CONTACT ON CONTACT_X_CUSTOMER.ID_CONTACT =
CONTACT.ID LEFT JOIN CUSTOMER ON CONTACT_X_CUSTOMER.ID_CUSTOMER = CUSTOMER.ID LEFT
JOIN ADDRESS ON IF(CONTACT_X_CUSTOMER.ID_ADDRESS > 0, ONTACT_X_CUSTOMER.ID_ADDRESS =
ADDRESS.ID, IF(CUSTOMER.ID_ADDRESS_SHIPTO > 0, CUSTOMER.ID_ADDRESS_SHIPTO =
ADDRESS.ID, CUSTOMER.ID_ADDRESS_MAIN = ADDRESS.ID)) LEFT JOIN ADDRESS
CONTACT_X_CUSTOMER_ADDRESS ON CONTACT_X_CUSTOMER.ID_ADDRESS =
CONTACT_X_CUSTOMER_ADDRESS.ID LEFT JOIN CITY CONTACT_X_CUSTOMER_CITY ON
CONTACT_X_CUSTOMER_ADDRESS.ID_CITY = CONTACT_X_CUSTOMER_CITY.ID LEFT JOIN PROVINCE
CONTACT_X_CUSTOMER_PROVINCE ON CONTACT_X_CUSTOMER_ADDRESS.ID_PROVINCE =
CONTACT_X_CUSTOMER_PROVINCE.ID LEFT JOIN COUNTRY CONTACT_X_CUSTOMER_COUNTRY ON
CONTACT_X_CUSTOMER_ADDRESS.ID_COUNTRY = CONTACT_X_CUSTOMER_COUNTRY.ID LEFT JOIN
ADDRESS SHIPTO_ADDRESS ON CUSTOMER.ID_ADDRESS_SHIPTO = SHIPTO_ADDRESS.ID LEFT JOIN
CITY SHIPTO_CITY ON SHIPTO_ADDRESS.ID_CITY = SHIPTO_CITY.ID LEFT JOIN PROVINCE
SHIPTO_PROVINCE ON SHIPTO_ADDRESS.ID_PROVINCE = SHIPTO_PROVINCE.ID LEFT JOIN COUNTRY
SHIPTO_COUNTRY ON SHIPTO_ADDRESS.ID_COUNTRY = SHIPTO_COUNTRY.ID LEFT JOIN ADDRESS
MAIN_ADDRESS ON CUSTOMER.ID_ADDRESS_MAIN = MAIN_ADDRESS.ID LEFT JOIN CITY MAIN_CITY ON
MAIN_ADDRESS.ID_CITY = MAIN_CITY.ID LEFT JOIN PROVINCE MAIN_PROVINCE ON
MAIN_ADDRESS.ID_PROVINCE = MAIN_PROVINCE.ID LEFT JOIN COUNTRY MAIN_COUNTRY ON
MAIN_ADDRESS.ID_COUNTRY = MAIN_COUNTRY.ID WHERE CONTACT_X_CUSTOMER.ID_CUSTOMER = 17
ORDER BY CONTACT.LastName
(of course checking for NULL and TRIM() sure adds to the select)
I originally had it looking much better...
LEFT JOIN ADDRESS ON IF(CONTACT_X_CUSTOMER.ID_ADDRESS > 0,
CONTACT_X_CUSTOMER.ID_ADDRESS = ADDRESS.ID, IF(CUSTOMER.ID_ADDRESS_SHIPTO > 0,
CUSTOMER.ID_ADDRESS_SHIPTO = ADDRESS.ID, CUSTOMER.ID_ADDRESS_MAIN = ADDRESS.ID)
This actually worked great, I only had to refer to ADDRESS once in the select for
output, I only had to join the other tables CITY, PROVINCE, COUNTRY once on ADDRESS
too.
BUT the darned thing wouldn't see the indexes on the related address fields, so when I
populated the ADDRESS table with 100000 records, the return took much longer than
desired. IF it did pay attention to the indexes like I expected, then it would have
been the much preferable choice, but it didn't, so I had to do many extra joins and
use aliases and get a monstrosity :)
( i do really appologize for that big spew of SQL )
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 02, 2004 11:37 AM
To: Luc Foisy; MYSQL-List (E-mail)
Subject: Re: JOINing complication, help please
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]
