Price, Randall wrote:
Could you use something like this (untried):
SELECT
CONCAT(COALESCE(r.first_name, ''), ' ',
COALESCE(r.last_name, ''), '\n',
COALESCE(r.organization, ''), '\n',
COALESCE(r.title, ''), '\n',
COALESCE(a.address1, ''), '\n',
COALESCE(a.city, ''), ', ',
COALESCE(a.state, ''), ' ',
COALESCE(a.zip, ''), '\n',
COALESCE(r.email, ''))
FROM
registrants r,
addresses a
WHERE
r.reg_id=121
this is good. though, if r.title is NULL I'll get an extra empty row on
screen:
john doe
doe, inc.
<-- no title, empty row
123 main st.
testtown, TE 12345
also, I would like to hear opinion about the following query:
SELECT o.col1, o.col2, o.col3,
(
SELECT CONCAT_WS('', r.first_name, ' ', r.last_name, '\n',
r.organization, '', r.title, '\n', a.address1, '\n', a.city, ', ',
a.state, ' ', a.zip, '\n', r.email, '\nHome: ', left(r.phone_home, 3),
'-', mid(r.phone_home, 3, 3), '-', right(r.phone_home, 4), '\nWork: ',
left(r.phone_work, 3), '-', mid(r.phone_work, 3, 3), '-',
right(r.phone_work, 4))
FROM registrants r, addresses a
WHERE r.reg_id=o.registered_id
and a.reg_id=r.reg_id
and a.address_type='Business'
LIMIT 1
) as REGISTERED_BY,
pm.payment_method as payment_method_name, f.form_name FORM_NAME
FROM orders o, payment_methods pm, forms f
WHERE o.order_id=".$order_id."
AND pm.pm_id=o.payment_method
AND f.form_id=".$form_id."
is it smart/good way to have subquery this way or solution below is
better one:
SELECT o.col1, o.col2, o.col3, r.first_name, r.last_name,
r.organization, r.title, a.address1, a.city, a.state, a.zip, r.email,
r.phone_home, r.phone_work, pm.payment_method as payment_method_name,
f.form_name FORM_NAME
FROM orders o, payment_methods pm, forms f
WHERE o.order_id=".$order_id."
AND pm.pm_id=o.payment_method
AND f.form_id=".$form_id."
AND r.reg_id=o.registered_id
AND a.reg_id=r.reg_id
AND a.address_type='Business'
in this case I have to create string REGISTERED_BY by php.
reason I did it as example 1 is because I read in few books and online
people saying do whatever you can using query in mysql rather then using
php. it's faster, better, more secure,...
thoughts?
-afan
Randall Price
Secure Enterprise Technology Initiatives
Microsoft Implementation Group
Virginia Tech Information Technology
1700 Pratt Drive
Blacksburg, VA 24060
-----Original Message-----
From: Afan Pasalic [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 14, 2008 11:53 AM
To: mysql@lists.mysql.com
Subject: CONCAT doesn't work with NULL?
hi,
I have query
SELECT CONCAT(r.first_name, ' ', r.last_name, '\n', r.organization,
'\n', r.title, '\n', a.address1, '\n', a.city, ', ', a.state, ' ',
a.zip, '\n', r.email)
FROM registrants r, addresses a
WHERE r.reg_id=121
if any of columns has value (e.g. title) NULL, I'll get as result 0
records.
If query doesn't have concat() - it works fine.
Why is that?
-afan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
