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