[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Re: which query solution is better?
From:       John Hicks <johnlist () gulfbridge ! net>
Date:       2008-07-08 16:20:16
Message-ID: 487393C0.4090504 () gulfbridge ! net
[Download RAW message or body]

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/mysql?unsub=mysql-marcsub@progressive-comp.com



[prev in list] [next in list] [prev in thread] [next in thread]