Re: Help with query

[Dušan Pavlica]

Thanks, Michal, for your help.
Your query works as I need.
I tried to use same syntax as in MS Access, but results are for some reason 
different for this query. I'm working on application which should be able to 
connect to MySQL or to MSAccess (users' choice) and I didn't want to write 
querries for each DB system separately. Now I see that I will have to.

Dusan



----- Original Message ----- 
From: "Michael Stassen" <[EMAIL PROTECTED]>
To: "Dušan Pavlica" <[EMAIL PROTECTED]>
Cc: "list mysql" <mysql@lists.mysql.com>
Sent: Wednesday, October 12, 2005 2:54 PM
Subject: Re: Help with query


Dušan Pavlica wrote:
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

I haven't really tried to figure out what mysql is doing with your query, 
but perhaps these lines from the manual 
<http://dev.mysql.com/doc/mysql/en/join.html> are relevant:

  In versions of MySQL prior to 5.0.1, parentheses in table_references 
were
  just omitted and all join operations were grouped to the left. In 
general,
  parentheses can be ignored in join expressions containing only inner 
join
  operations. As of 5.0.1, nested joins are allowed (see Section 7.2.10,
  “How MySQL Optimizes Nested Joins”).

In any case, does this do what you want?

  SELECT p.Description, p.ID, pp.Product_ID, pc.Code, pp.StartDate
  FROM products_prices pp
  JOIN products p ON p.ID = pp.Product_ID
  LEFT JOIN products_codes pc ON p.ID = pc.Product_ID AND pc.CodeType = 1
  WHERE StartDate < Now()
  ORDER BY p.ID, pp.StartDate DESC;

Michael


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED] 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]