Hello,
could someone help me please to construct correct query or tell me what I'm
doing wrong?
I have three tables:
table products
table products_codes where some products could have assigned another additional
codes
table products_prices
I want to get all rows from product_prices listed with product Description and
Code for particular CodeType
And here is the create script, sample data and the query:
CREATE TABLE `products` (
`ID` int(10) unsigned NOT NULL auto_increment,
`Description` varchar(50) NOT NULL default '',
`Units` varchar(10) NOT NULL default '',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB;
INSERT INTO products VALUES(NULL, "Product 1", "lt");
INSERT INTO products VALUES(NULL, "Product 2", "lt");
INSERT INTO products VALUES(NULL, "Product 3", "lt");
CREATE TABLE `products_codes` (
`Product_ID` int(10) unsigned NOT NULL default '0',
`Code` varchar(50) NOT NULL default '',
`CodeType` tinyint NOT NULL default '',
PRIMARY KEY (`Product_ID`,`CodeType`)
) ENGINE=InnoDB;
INSERT INTO products_codes VALUES(1, "ABC", 1);
INSERT INTO products_codes VALUES(2, "XYZ", 1);
CREATE TABLE `products_prices` (
`Product_ID` int(10) unsigned NOT NULL default '0',
`StartDate` datetime NOT NULL default '0000-00-00 00:00:00',
`Price` double NOT NULL default '0',
PRIMARY KEY (`Product_ID`,`StartDate`)
) ENGINE=InnoDB;
INSERT INTO products_prices VALUES(1, '20050901000000', 20);
INSERT INTO products_prices VALUES(1, '20051001000000', 25);
INSERT INTO products_prices VALUES(1, '20051101000000', 30);
INSERT INTO products_prices VALUES(2, '20051001000000', 15);
INSERT INTO products_prices VALUES(3, '20051001000000', 10);
SELECT p.Description, p.ID, pp.Product_ID, pc.Code, pp.StartDate FROM
products_prices pp
INNER JOIN (products p
LEFT JOIN products_codes pc ON p.ID = pc.Product_ID AND pc.CodeType = 1)
ON p.ID = pp.Product_ID
where StartDate < Now()
ORDER BY p.ID, pp.StartDate desc
Here is the result of the query:
"Description","ID","Product_ID","Code","StartDate"
"Product 1",1,3,NULL,"2005-10-01 00:00:00"
"Product 1",1,1,"ABC","2005-10-01 00:00:00"
"Product 1",1,2,NULL,"2005-10-01 00:00:00"
"Product 1",1,1,"ABC","2005-09-01 00:00:00"
"Product 2",2,2,"XYZ","2005-10-01 00:00:00"
"Product 2",2,3,NULL,"2005-10-01 00:00:00"
"Product 2",2,1,NULL,"2005-10-01 00:00:00"
"Product 2",2,1,NULL,"2005-09-01 00:00:00"
"Product 3",3,2,NULL,"2005-10-01 00:00:00"
"Product 3",3,3,NULL,"2005-10-01 00:00:00"
"Product 3",3,1,NULL,"2005-10-01 00:00:00"
"Product 3",3,1,NULL,"2005-09-01 00:00:00"
I don't know why this query returns also rows where p.ID != pp.ProductID
And another thing. If I remove from selected columns pp.Product_ID query
returns error:
Column 'Product_ID' in field list is ambiguous (ErrorNr. 1052). Why??
MySQL 4.1.14, WinXP
Thanks a lot in advance for any help
Kind regards,
Dusan Pavlica
