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 |