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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
