This is how your tables should be:<br><br><b>products</b><br>productid<br>name<br>categoryid<br>manufacturer<br><br><br><b>clients</b><br>clientid<br>name<br>address<br><br><br><b>category</b><br>categoryid<br>title<br>active<br>
<br><b>client_product</b><br>superid<br>clientid<br>productid<br>quantity<br>date<br><br><b>Example of your query:</b><br><br>select p.productid, <a href="http://p.name">p.name</a>, p.categoryid, p.manufacturer, c.categoryid, <a href="http://c.name">c.name</a>, c.address, s.clientid, s.title, s.active, cl.superid, cl.clientid, cl.productid, cl.quantity, cl.date from <a href="http://client_product.cl">client_product.cl</a> <br>
INNER JOIN  product p ON cl.productid=p.productid<br>INNER JOIN  client s ON cl.clientid=s.clientid<br>ORDER BY cl.superid desc<br><br><b>Keynote: </b><br><br>p- products<br>c- category<br>s- client<br>cl- client_product<br>
<br>Hope it will be of help<br><br><div class="gmail_quote">On Tue, Oct 9, 2012 at 9:49 AM, Victor Kisovi <span dir="ltr">&lt;<a href="mailto:victor.kisovi@gmail.com" target="_blank">victor.kisovi@gmail.com</a>&gt;</span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">@Yegon you are on point. This problem simply requires a join statement. <div><br></div><div><br></div><div>I once did this: <div>
<br></div><div>mysql&gt; select* from (select &#39;child&#39;, count(idchild)num_rows from child) c join (select &#39;parent&#39;, count(idparent)num_rows from parent) p;</div>
<div><br></div><div>And the output was:</div><div><br></div><div><div>+-------+---------------+------------+-----------------------+</div><div>| child | num_rows | parent   | num_rows        |</div><div>+-------+----------------+-----------+-----------------------+</div>

<div>| child |            41 | parent   |       41              |</div><div>+-------+---------------+------------+------------------------+</div></div><div><br></div><div><br></div><div><div><div class="h5"><br><br><div class="gmail_quote">
On Tue, Oct 9, 2012 at 8:33 AM, Victor Yegon <span dir="ltr">&lt;<a href="mailto:viktayeg@gmail.com" target="_blank">viktayeg@gmail.com</a>&gt;</span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Hey you should do select statement to join the four tables <br><br><div class="gmail_quote"><div><div>On Mon, Oct 8, 2012 at 8:58 PM, Nd&#39;wex Common <span dir="ltr">&lt;<a href="mailto:flexycat@gmail.com" target="_blank">flexycat@gmail.com</a>&gt;</span> wrote:<br>


</div></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div>Hi,<br><br>I have some tables as follows<br><br><b>products</b><br>id<br>name<br>categoryid<br>
manufacturer<br><br><b>clients</b><br>
id<br>name<br>address<br><br><b>category</b><br>id<br>title<br>active<br><br><b>client_product</b><br>
id<br>clientid<br>productid<br>quantity<br>date<br><br>from the above structures a report is to be generated as follows<br><br>date | product1 | product2 | product3 | ---- | product x<br><br>under each product are the quantities acquired. each client may have different products.<br>



<br>question is how do i extract the data in the above format using possibly a single query?<br><br>thank you in advance<br><br>
<br></div></div>_______________________________________________<br>
Skunkworks mailing list<br>
<a href="mailto:Skunkworks@lists.my.co.ke" target="_blank">Skunkworks@lists.my.co.ke</a><br>
------------<br>
List info, subscribe/unsubscribe<br>
<a href="http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks" target="_blank">http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks</a><br>
------------<br>
<br>
Skunkworks Rules<br>
<a href="http://my.co.ke/phpbb/viewtopic.php?f=24&amp;t=94" target="_blank">http://my.co.ke/phpbb/viewtopic.php?f=24&amp;t=94</a><br>
------------<br>
Other services @ <a href="http://my.co.ke" target="_blank">http://my.co.ke</a><span><font color="#888888"><br></font></span></blockquote></div><span><font color="#888888"><br><br clear="all">
<br>-- <br>Regards,<br><br>Yegon Victor | Web Specialist/Internet Consultant | Extra IT Solutions<br>
<br>Website:<a style="color:rgb(51,51,255)" href="http://www.extraitsolutions.com" target="_blank">http://www.extraitsolutions.com</a> |<br><br><br><br><br>
</font></span><br>_______________________________________________<br>
Skunkworks mailing list<br>
<a href="mailto:Skunkworks@lists.my.co.ke" target="_blank">Skunkworks@lists.my.co.ke</a><br>
------------<br>
List info, subscribe/unsubscribe<br>
<a href="http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks" target="_blank">http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks</a><br>
------------<br>
<br>
Skunkworks Rules<br>
<a href="http://my.co.ke/phpbb/viewtopic.php?f=24&amp;t=94" target="_blank">http://my.co.ke/phpbb/viewtopic.php?f=24&amp;t=94</a><br>
------------<br>
Other services @ <a href="http://my.co.ke" target="_blank">http://my.co.ke</a><br></blockquote></div><br><br clear="all"><div><br></div>-- <br>Regards,</div></div><span class="HOEnZb"><font color="#888888"><div><div>Victor Kisovi</div>
</div><div><a href="tel:%2B254%20726%20723%20360" value="+254726723360" target="_blank">+254 726 723 360</a></div><br>
</font></span></div></div>
<br>_______________________________________________<br>
Skunkworks mailing list<br>
<a href="mailto:Skunkworks@lists.my.co.ke">Skunkworks@lists.my.co.ke</a><br>
------------<br>
List info, subscribe/unsubscribe<br>
<a href="http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks" target="_blank">http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks</a><br>
------------<br>
<br>
Skunkworks Rules<br>
<a href="http://my.co.ke/phpbb/viewtopic.php?f=24&amp;t=94" target="_blank">http://my.co.ke/phpbb/viewtopic.php?f=24&amp;t=94</a><br>
------------<br>
Other services @ <a href="http://my.co.ke" target="_blank">http://my.co.ke</a><br></blockquote></div><br><br clear="all"><br>-- <br>Regards,<br><br>Yegon Victor | Web Specialist/Internet Consultant | Extra IT Solutions<br>
<br>Website:<a style="color:rgb(51,51,255)" href="http://www.extraitsolutions.com" target="_blank">http://www.extraitsolutions.com</a> |<br><br><br><br><br>