[RESOLVED] selecting distinct product id using order id

steamPunk

i can get a list of ids for orders placed for a specific date from my db table 'commandes' using

"SELECT comm_id FROM commandes WHERE ((TO_DAYS(comm_jour)-TO_DAYS(NOW()))=10)"

this will return a list of order id numbers for that date

i have another table 'commdetail' containing the products ordered for each order id - so each line has an order id ('comm_id'), a product id ('pr_id') and a quantity ('pr_quantite')

i can get a list of product ids for each order using

"SELECT pr_id FROM commdetail WHERE comm_id='$comm_id'"

what i would like to do is to combine the two queries into one query - so that i can get a list of distinct product ids from the table 'commdetail' using the order ids for a specific date in the table 'commandes'

do you think this is possible ?

thanks

rulian

use JOIN

SELECT
 DISTINCT(`t2`.`pr_id`), `t1`.`comm_id`
FROM `commandes` AS `t1`
LEFT JOIN `commdetail` AS `t2` on `t1`.`comm_id` = `t2`.`comm_id`
WHERE ((TO_DAYS(comm_jour)-TO_DAYS(NOW()))=10)

steamPunk

that was quick !

I've never used JOIN before and i guess this is the moment to start

thank you very much

P.S. is it correct to do this (adding DISTINCT):

SELECT
t1.comm_id, DISTINCT t2.pr_id
FROM commandes AS t1
LEFT JOIN commdetail AS t2 on t1.comm_id = t2.comm_id
WHERE ((TO_DAYS(comm_jour)-TO_DAYS(NOW()))=10)

rulian

not sure, the order I did might comprimise the sql optimizer, since I did do a left join, but as I understand the SELECT [return variables ] dont really effect how the sql is performed, since the return variables can vary greatly

steamPunk

ok - i'll give it a try

thanks again for your help

steamPunk

it doesn't seem to want to output a distinct list of product ids here's what i get :

43
216
43
46
60
62
216
304

i tried to use "DESCRIBE commandes" to post the table structure on here but it outputs and html table - how can i ouput the structure in a text format (like in the manual)??

steamPunk

the 'commandes' table looks like this

CREATE TABLE commandes (
comm_id int(11) NOT NULL auto_increment,
client_id mediumint(9) NOT NULL default '0',
comm_date date NOT NULL default '0000-00-00',
comm_livre tinyint(4) NOT NULL default '0',
comm_total decimal(6,2) NOT NULL default '0.00',
comm_pan tinyint(4) NOT NULL default '0',
comm_corb tinyint(4) NOT NULL default '0',
pan_prix decimal(6,2) NOT NULL default '0.00',
pan_content mediumtext NOT NULL,
corb_prix decimal(6,2) NOT NULL default '0.00',
ami_comm tinytext NOT NULL,
comm_jour date NOT NULL default '0000-00-00',
comm_jour_num tinytext NOT NULL,
KEY comm_id (comm_id,client_id)
)

and the 'commdetail' table looks like this

CREATE TABLE commdetail (
comm_id mediumint(9) NOT NULL default '0',
pr_id mediumint(9) NOT NULL default '0',
pr_quantite int(11) NOT NULL default '0',
pr_prix decimal(6,2) NOT NULL default '0.00',
pr_nom mediumtext NOT NULL,
pr_unite varchar(12) NOT NULL default '',
pr_ref varchar(12) NOT NULL default '',
KEY comm_id (comm_id,pr_id)
)

steamPunk

anyone got any ideas at all how i can structure this to get a set of unique distinct results without any repeated product id's ?

thanks 🙂

bpat1434

GROUP BY is your friend 😉

SELECT
`t2`.`pr_id`, `t1`.`comm_id`
FROM `commandes` AS `t1`
LEFT JOIN `commdetail` AS `t2` on `t1`.`comm_id` = `t2`.`comm_id`
WHERE ((TO_DAYS(comm_jour)-TO_DAYS(NOW()))=10)
GROUP BY t2.`pr_id`

Assuming of course that pr_id is your order ID, and that it's a 1 to 1 relationship between the order and order-detail (i.e. no order number will be used with more than one order).

steamPunk

you're spot on - that's perfect !

this one has really been doing my head in - and this one solution will avoid making a lot of individual queries

thank you 🙂