> 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
You might not have normalized the above table properly. Why do you
need client_product table for example? Capture clients stock? Or is
it order_table? And even if there is a good reason, unless you make
client_product.clientid and client_product.productid foreign keys,
your database is bound to be inconsistent. Anyway in my opinion,
think of your normalization again. Something is not properly mapped
and hence your difficulties.
>
> 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.
Don't follow your question but I am guessing you want a list of
products with their date? I would joint client_product, client and
product table and sort by date then
select date, productid, quantity from client_product join clients on
clients.id = client_product.clientid join product on
client_product.productid = product.id order by date DESC;
This will join the client table to get the client name, product table
to get the product name. The main table is client_product. The
foreign key assumption started it above is implied in the query
William
>
> question is how do i extract the data in the above format using possibly a
> single query?
>
> thank you in advance
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL: <http://lists.my.co.ke/cgi-bin/mailman/private/skunkworks/attachments/20121008/587ecd45/attachment.htm>
>
> ------------------------------
> http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks
> ------------
> Skunkworks Server donations spreadsheet
> http://spreadsheets.google.com/ccc?key=0AopdHkqSqKL-dHlQVTMxU1VBdU1BSWJxdy1fbjAwOUE&hl=en
> End of Skunkworks Digest, Vol 32, Issue 46
> ******************************************