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.