@Yegon you are on point. This problem simply requires a join statement.
I once did this:
mysql> select* from (select 'child', count(idchild)num_rows from child) c join (select 'parent', count(idparent)num_rows from parent) p;
And the output was:
+-------+---------------+------------+-----------------------+ | child | num_rows | parent | num_rows | +-------+----------------+-----------+-----------------------+ | child | 41 | parent | 41 | +-------+---------------+------------+------------------------+
Sub queries have a horrible performance. Try avoid them especially if you happen to be using MySQL Try running the same query, one using join and the other a sub query and see the difference
On Tue, Oct 9, 2012 at 8:33 AM, Victor Yegon <viktayeg@gmail.com> wrote:
Hey you should do select statement to join the four tables
On Mon, Oct 8, 2012 at 8:58 PM, Nd'wex Common <flexycat@gmail.com> wrote:
Hi,
I have some tables as follows
*products* id name categoryid manufacturer
*clients* id name address
*category* id title active
*client_product* id clientid productid quantity date
from the above structures a report is to be generated as follows
date | product1 | product2 | product3 | ---- | product x
under each product are the quantities acquired. each client may have different products.
question is how do i extract the data in the above format using possibly a single query?
thank you in advance
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Regards,
Yegon Victor | Web Specialist/Internet Consultant | Extra IT Solutions
Website:http://www.extraitsolutions.com |
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Regards, Victor Kisovi +254 726 723 360