----- Original Message ----
From: John Hicks <[EMAIL PROTECTED]>
To: Lamp Lists <[EMAIL PROTECTED]>
Cc: mysql@lists.mysql.com
Sent: Tuesday, July 8, 2008 11:20:16 AM
Subject: Re: which query solution is better?
Lamp Lists wrote:
> hi,
> I would like to get your opinions regarding which query you think is better
> solution and, of course - why.
> I have (very simplified example) 3 tables: orders, members and addresses
> I need to show order info for specific order_id,
> solution 1:
> select ordered_by, order_date, payment_method, order_status
>
> from orders
> where order_id=123
> select m.name, a.address, a.city, a.state, a.zip
> from members m, addresses a
> where m.member_id=$ordered_by and a.address_id=m.address_id
> //$ordered_by is value from first query
> solution 2:
> select ordered_by, order_date, payment_method, order_status, (select m.name,
> a.address, a.city, a.state, a.zip from members m, addresses a where
> m.member_id=ordered_by and a.address_id=m.address_id)
>
> from orders
> where order_id=123
> (queries are written without testing and maybe it doesn't work exactly, but
> it's more to gave you the idea what I'm talking about :D)
> also,what if I have to list 20,50 or 100 orders instead one order? would be
> subquery still be an option?
> thanks for any opinion.
> -ll
>
I don't understand what syntax you're using for your second solution.
Your first solution uses two separate queries which will accomplish the
task.
They could be combined into a single query like this:
select * from orders
left join members on member_id = ordered_by
left join addresses on addresses.address_id = members.address_id
where order_id = 123
-- john
sorry john. my bad. I should test the query before I post it because it doesn't
work that way :D
though, let me modify the question:
solution 1:
select o.ordered_by, o.order_date, o.payment_method, o.order_status,
concat(m.first_name, ' ', m.last_name) name
left join members m on m.member_id=o.ordered_by
from orders o
where o.order_id=123
vs.
select o.ordered_by, o.order_date, o.payment_method, o.order_status, (select
concat(first_name, ' ', last_name) name from members where
member_id=o.registered_by) name
from orders o
where o.order_id=123
in first solution there is join and in second subquery. what's better and why?
sorry for this mess :D
-ll
