List:General Discussion« Previous MessageNext Message »
From:John Hicks Date:July 8 2008 4:20pm
Subject:Re: which query solution is better?
View as plain text  
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
Thread
which query solution is better?Lamp Lists8 Jul
  • Re: which query solution is better?John Hicks8 Jul
Re: which query solution is better?Lamp Lists8 Jul
  • Re: which query solution is better?Ananda Kumar9 Jul