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:afan@stripped]
> Sent: Wednesday, May 14, 2008 11:53 AM
> To: mysql@stripped
> 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
>
