Description:
I have that tables:
--- orders ---
id int(5) NOT NULL auto_increment,
price_id int(3) default NULL,
manager_id int(3) default NULL,
dealer_id int(5) default NULL,
code char(8) default NULL,
status int(1) default NULL,
created datetime default NULL,
modified timestamp NOT NULL,
PRIMARY KEY (id)
--- ordered ---
id int(8) NOT NULL auto_increment,
order_id int(5) default NULL,
model_id int(7) default NULL,
count int(10) default NULL,
created datetime default NULL,
modified timestamp NOT NULL,
PRIMARY KEY (id)
--- factor ---
id int(8) NOT NULL auto_increment,
dealer_id int(5) default NULL,
price_id int(3) default NULL,
coefficient float(10,5) default NULL,
modified timestamp NOT NULL,
PRIMARY KEY (id)
--- price ---
id int(7) NOT NULL auto_increment,
price_id int(3) default NULL,
group_id int(3) default NULL,
model varchar(40) NOT NULL default '',
description varchar(255) default NULL,
price float(8,3) default NULL,
monetary_id int(2) default NULL,
formula varchar(255) default NULL,
image varchar(100) default NULL,
url varchar(255) default NULL,
document blob,
document_type enum('plain','html') default NULL,
created datetime default NULL,
created_by int(3) default NULL,
modified timestamp NOT NULL,
modified_by int(3) default NULL,
PRIMARY KEY (id),
UNIQUE KEY model (model)
--- dealers ---
id int(5) NOT NULL auto_increment, .....
My query is:
SELECT
price.model,
orders.id,
orders.code,
orders.dealer_id,
price.price_id,
(SELECT coefficient FROM factor WHERE dealer_id = 12 AND price_id = price.price_id) AS coefficient
FROM ordered
LEFT JOIN orders ON ordered.order_id = orders.id
LEFT JOIN price ON ordered.model_id = price.id
WHERE orders.code = 'oOjV8ooO';
I understant my query is not ture. And for my result query must be other. I know that .. now.
But MySQL with this query must return result such as I want too.
How to repeat:
model id code dealer_id price_id coefficient
ENW-9606 1 oOjV8ooO 1 1 1.06000
SW-501 1 oOjV8ooO 1 1 NULL
CISCOSOHO76 1 oOjV8ooO 1 2 NULL
CISCOSOHO77H 1 oOjV8ooO 1 2 NULL
CISCOSOHO78 1 oOjV8ooO 1 2 NULL
Coefficient in first row is true. But in other rows coefficient must be not NULL!
Bug? I'm don't known.
Suggested fix:
model id code dealer_id price_id coefficient
ENW-9606 1 oOjV8ooO 1 1 1.06000
SW-501 1 oOjV8ooO 1 1 1.06000
CISCOSOHO76 1 oOjV8ooO 1 2 1.00000
CISCOSOHO77H 1 oOjV8ooO 1 2 1.00000
CISCOSOHO78 1 oOjV8ooO 1 2 1.00000
I'm so sorry for my bad english.
Description: I have that tables: --- orders --- id int(5) NOT NULL auto_increment, price_id int(3) default NULL, manager_id int(3) default NULL, dealer_id int(5) default NULL, code char(8) default NULL, status int(1) default NULL, created datetime default NULL, modified timestamp NOT NULL, PRIMARY KEY (id) --- ordered --- id int(8) NOT NULL auto_increment, order_id int(5) default NULL, model_id int(7) default NULL, count int(10) default NULL, created datetime default NULL, modified timestamp NOT NULL, PRIMARY KEY (id) --- factor --- id int(8) NOT NULL auto_increment, dealer_id int(5) default NULL, price_id int(3) default NULL, coefficient float(10,5) default NULL, modified timestamp NOT NULL, PRIMARY KEY (id) --- price --- id int(7) NOT NULL auto_increment, price_id int(3) default NULL, group_id int(3) default NULL, model varchar(40) NOT NULL default '', description varchar(255) default NULL, price float(8,3) default NULL, monetary_id int(2) default NULL, formula varchar(255) default NULL, image varchar(100) default NULL, url varchar(255) default NULL, document blob, document_type enum('plain','html') default NULL, created datetime default NULL, created_by int(3) default NULL, modified timestamp NOT NULL, modified_by int(3) default NULL, PRIMARY KEY (id), UNIQUE KEY model (model) --- dealers --- id int(5) NOT NULL auto_increment, ..... My query is: SELECT price.model, orders.id, orders.code, orders.dealer_id, price.price_id, (SELECT coefficient FROM factor WHERE dealer_id = 12 AND price_id = price.price_id) AS coefficient FROM ordered LEFT JOIN orders ON ordered.order_id = orders.id LEFT JOIN price ON ordered.model_id = price.id WHERE orders.code = 'oOjV8ooO'; I understant my query is not ture. And for my result query must be other. I know that .. now. But MySQL with this query must return result such as I want too. How to repeat: model id code dealer_id price_id coefficient ENW-9606 1 oOjV8ooO 1 1 1.06000 SW-501 1 oOjV8ooO 1 1 NULL CISCOSOHO76 1 oOjV8ooO 1 2 NULL CISCOSOHO77H 1 oOjV8ooO 1 2 NULL CISCOSOHO78 1 oOjV8ooO 1 2 NULL Coefficient in first row is true. But in other rows coefficient must be not NULL! Bug? I'm don't known. Suggested fix: model id code dealer_id price_id coefficient ENW-9606 1 oOjV8ooO 1 1 1.06000 SW-501 1 oOjV8ooO 1 1 1.06000 CISCOSOHO76 1 oOjV8ooO 1 2 1.00000 CISCOSOHO77H 1 oOjV8ooO 1 2 1.00000 CISCOSOHO78 1 oOjV8ooO 1 2 1.00000 I'm so sorry for my bad english.