(To the esteemed members of the MySQL mailing list, my sincere apologies
for the length of this post-- SG)
Luc,
You are killing yourself with all of those decisions.
Take this for example:
, 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
)
)
)
)
can all be condensed to
RTRIM(ISNULL(CONCAT(' ',if(CONTACT_X_CUSTOMER.ID_ADDRESS >
0,CONTACT_X_CUSTOMER_ADDRESS.AddrLine2, IF(CUSTOMER.ID_ADDRESS_SHIPTO > 0,
SHIPTO_ADDRESS.AddrLine2, MAIN_ADDRESS.AddrLine2))),''))
Breaking that down from the inside out:
Choose which field to return based on the available IDs: IF(IF())
Add leading spaces: CONCAT()
Check for NULL. If it is, make it an empty string: ISNULL()
Collapse any remaining strings that are all blanks to an empty string but
leave your leading spaces: RTRIM()
Your JOINs seem OK, you have 3 sets of addresses to check and 4 tables per
address so 12 joins is not unusual for your data set. I am not fond of the
big IF() clause in your 3rd join but I think you said in a follow-up letter
that you didn't mean for that to be in there so I am not sure if I need to
comment on it or not.
You may end up with a faster set of results and code that's easier to
maintain if you UNION the three sets of addresses together rather than try
to return everything all at once. Something like:
(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'
, ADDRESS.AddrLine1
+ RTRIM(CONCAT(' ',ISNULL(ADDRESS.AddrLine2,'')))
+ RTRIM(CONCAT(' ',ISNULL(ADDRESS.AddrLine3,''))) as 'Address'
, RTRIM(CONCAT(' ',ISNULL(CITY.Name,''))) as 'City'
, RTRIM(CONCAT(' ',ISNULL(PROVINCE.Name,''))) as 'Province'
, RTRIM(CONCAT(' ',ISNULL(COUNTRY.Name,''))) as 'Country'
, RTRIM(CONCAT(' ',ISNULL(ADDRESS.PostalCode,''))) as 'Postal Code'
. 'Contact Address' 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 CONTACT_X_CUSTOMER.ID_ADDRESS = ADDRESS.ID
LEFT JOIN CITY
ON ADDRESS.ID_CITY = CITY.ID
LEFT JOIN PROVINCE
ON ADDRESS.ID_PROVINCE = PROVINCE.ID
LEFT JOIN COUNTRY
ON ADDRESS.ID_COUNTRY = COUNTRY.ID
WHERE CONTACT_X_CUSTOMER.ID_CUSTOMER = 17
AND CONTACT_X_CUSTOMER.ID_ADDRESS > 0
)
UNION
(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'
, ADDRESS.AddrLine1
+ RTRIM(CONCAT(' ',ISNULL(ADDRESS.AddrLine2,'')))
+ RTRIM(CONCAT(' ',ISNULL(ADDRESS.AddrLine3,''))) as 'Address'
, RTRIM(CONCAT(' ',ISNULL(CITY.Name,''))) as 'City'
, RTRIM(CONCAT(' ',ISNULL(PROVINCE.Name,''))) as 'Province'
, RTRIM(CONCAT(' ',ISNULL(COUNTRY.Name,''))) as 'Country'
, RTRIM(CONCAT(' ',ISNULL(ADDRESS.PostalCode,''))) as 'Postal Code'
. 'Customer Ship To' 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 CUSTOMER.ID_ADDRESS_SHIPTO = ADDRESS.ID
LEFT JOIN CITY
ON ADDRESS.ID_CITY = CITY.ID
LEFT JOIN PROVINCE
ON ADDRESS.ID_PROVINCE = PROVINCE.ID
LEFT JOIN COUNTRY
ON ADDRESS.ID_COUNTRY = COUNTRY.ID
WHERE CONTACT_X_CUSTOMER.ID_CUSTOMER = 17
AND NOT CONTACT_X_CUSTOMER.ID_ADDRESS>0
AND CUSTOMER.ID_ADDRESS_SHIPTO > 0
)
UNION
(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'
, ADDRESS.AddrLine1
+ RTRIM(CONCAT(' ',ISNULL(ADDRESS.AddrLine2,'')))
+ RTRIM(CONCAT(' ',ISNULL(ADDRESS.AddrLine3,''))) as 'Address'
, RTRIM(CONCAT(' ',ISNULL(CITY.Name,''))) as 'City'
, RTRIM(CONCAT(' ',ISNULL(PROVINCE.Name,''))) as 'Province'
, RTRIM(CONCAT(' ',ISNULL(COUNTRY.Name,''))) as 'Country'
, RTRIM(CONCAT(' ',ISNULL(ADDRESS.PostalCode,''))) as 'Postal Code'
. '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 CONTACT_X_CUSTOMER.ID_ADDRESS_MAIN = ADDRESS.ID
LEFT JOIN CITY
ON ADDRESS.ID_CITY = CITY.ID
LEFT JOIN PROVINCE
ON ADDRESS.ID_PROVINCE = PROVINCE.ID
LEFT JOIN COUNTRY
ON ADDRESS.ID_COUNTRY = COUNTRY.ID
WHERE CONTACT_X_CUSTOMER.ID_CUSTOMER = 17
AND NOT CONTACT_X_CUSTOMER.ID_ADDRESS>0
AND NOT CUSTOMER.ID_ADDRESS_SHIPTO>0
)
ORDER BY `Last Name`
As you can see, there are only a couple of differences between each of the
queries in this UNION. One is which field is used to create the LEFT JOIN
for the ADDRESS table. The other is in the WHERE clause for each SELECT.
The last column, 'Address Type', is returning a constant for each of the
inner queries as each query should only return one type of address. The
parentheses surrounding the inner queries are required in order to use the
final ORDER BY clause.
This style of design will allow you to debug each inner query individually
and because it's based on the same template for each element of the UNION
should be easier to maintain. One new idea just hit me. We are doing an
awful lot of string manipulations during the course of this query. You may
want to experiment with your rendering language (if this query is to be
reprocessed for output as say HTML or a text file) to see if it can mangle
those strings faster.
Another way of avoiding so many string manipulations on the initial query
is to do two queries in sequence. The first query would select only the ID
number of the address we need (<the fields we need from the CONTACT and
CUSTOMER TABLES>, ID_ADDRESS, and 'Address Type') into a temp table then
you do the full set of JOINS on the temp table to fill in the rest of the
textual address information. Something like this:
CREATE TEMPORARY TABLE tmpContactIDS
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'
, IF(CONTACT_X_CUSTOMER.ID_ADDRESS > 0, ID_ADDRESS,
IF(CUSTOMER.ID_ADDRESS_SHIPTO>0,ID_ADDRESS_SHIPTO,ID_ADDRESS_MAIN)) as
'ADDRESS_ID'
, IF(CONTACT_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
WHERE CONTACT_X_CUSTOMER.ID_CUSTOMER = 17
SELECT ID
,`Last Name`
, `First Name`
, `Email`
, `Active`
, ADDRESS.AddrLine1
+ RTRIM(CONCAT(' ',ISNULL(ADDRESS.AddrLine2,'')))
+ RTRIM(CONCAT(' ',ISNULL(ADDRESS.AddrLine3,''))) as 'Address'
, RTRIM(CONCAT(' ',ISNULL(CITY.Name,''))) as 'City'
, RTRIM(CONCAT(' ',ISNULL(PROVINCE.Name,''))) as 'Province'
, RTRIM(CONCAT(' ',ISNULL(COUNTRY.Name,''))) as 'Country'
, RTRIM(CONCAT(' ',ISNULL(ADDRESS.PostalCode,''))) as 'Postal Code'
FROM tmpContactIDS IDS
LEFT JOIN ADDRESS
ON IDS.ADDRESS_ID = ADDRESS.ID
LEFT JOIN CITY
ON ADDRESS.ID_CITY = CITY.ID
LEFT JOIN PROVINCE
ON ADDRESS.ID_PROVINCE = PROVINCE.ID
LEFT JOIN COUNTRY
ON ADDRESS.ID_COUNTRY = COUNTRY.ID
That way you only have to hit the database twice, once to get the header
info (everything but the actual address info) then once again to resolve
the ID of the address you selected. The second hit is *much* lighter as you
are JOINING the address information tables on a *much* smaller result set.
I do have one nagging question, though.... Why do you insist on LEFT
joining the CONTACT and CUSTOMER tables to CONTACT_X_CUSTOMER? I would
assume that if a record exists in CONTACT_X_CUSTOMER then there would be
corresponding records in both of those tables.
I know this has been a huge post but you have a rather involved query to
work through. If the email system re-wrapped everything to that it became
illegible email me directly and I will attach these as text files and mail
them straight back.
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Luc Foisy"
<[EMAIL PROTECTED] To: <[EMAIL PROTECTED]>,
"MYSQL-List (E-mail)"
-magic.com> <[EMAIL PROTECTED]>
cc:
06/02/2004 02:09 PM Fax to:
Subject: RE: JOINing complication,
help please
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', -----------------snip----------------
= 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]
