Re: [Skunkworks] SQL question
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
I think it is well normalized, assuming products is a product reference and client_product maintains product purchases. I have not tested this but try select client_product.date, client_product.quantity, products.name from client_product left outer join products on client_product.productid = products.id; The left outer join is simply to bring you the whole list from client_product, just to highlight errors you may have, e.g in case you have an entry in clients_product that has no productid. I also read that it may be faster than the default inner join (which is supposed to be faster) On Tue, Oct 9, 2012 at 9:37 AM, William Muriithi <william.muriithi@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
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
Oh, I see you now have a variety of choice. On Tue, Oct 9, 2012 at 10:17 AM, Frankline Chitwa <frank.chitwa@gmail.com>wrote:
I think it is well normalized, assuming products is a product reference and client_product maintains product purchases. I have not tested this but try
select client_product.date, client_product.quantity, products.name from client_product left outer join products on client_product.productid = products.id;
The left outer join is simply to bring you the whole list from client_product, just to highlight errors you may have, e.g in case you have an entry in clients_product that has no productid. I also read that it may be faster than the default inner join (which is supposed to be faster)
On Tue, Oct 9, 2012 at 9:37 AM, William Muriithi < william.muriithi@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
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
thank you all for the responses given, my apologies for the "long" silence, will be trying the solutions herein as well as indicate the final solution.
participants (3)
-
Frankline Chitwa -
Nd'wex Common -
William Muriithi