Opps Apology, seem like Victor has responded. Was reading my mails from the oldest so had not see your response. Sorry for duplication
This is how your tables should be:
*products* productid name categoryid manufacturer
*clients* clientid name address
*category* categoryid title active
*client_product* superid clientid productid quantity date
*Example of your query:*
select p.productid, p.name, p.categoryid, p.manufacturer, c.categoryid, c.name, c.address, s.clientid, s.title, s.active, cl.superid, cl.clientid, cl.productid, cl.quantity, cl.date from client_product.cl INNER JOIN product p ON cl.productid=p.productid INNER JOIN client s ON cl.clientid=s.clientid ORDER BY cl.superid desc
*Keynote: *
p- products c- category s- client cl- client_product
Hope it will be of help
On Tue, Oct 9, 2012 at 9:49 AM, Victor Kisovi <victor.kisovi@gmail.com>wrote:
@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 | +-------+---------------+------------+------------------------+
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
_______________________________________________ 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 |