subquery or joins? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

subquery or joins?

Author

Topic

nego78
Starting Member

5 Posts

Posted - 2004-06-14 : 04:38:41

i've got 2 tables

CREATE TABLE `sklep_Produkty` (
`ID` int(10) unsigned NOT NULL auto_increment,
`Name` varchar(255) NOT NULL default '',
`Desc` text,
PRIMARY KEY (`ID`)
)

CREATE TABLE `sklep_CenyProduktow` (
`ID` int(10) unsigned NOT NULL auto_increment,
`IDProduct` int(10) unsigned NOT NULL default '0',
`Price` float(7,4) unsigned NOT NULL default '0.0000',
`DateOfChange` date NOT NULL default '2004-12-12',
PRIMARY KEY (`ID`)
)

i've got some products in first tabel
in second table i keep track of price changes

and i'd like to show records from
sklep_Produkty and join to every product the newest price from sklep_CenyProduktow joined by IDProduct

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-06-14 : 04:46:22

SELECT P.ID, P.Name, P.Desc, C.Price, C.DateOfChange
FROM sklep_Produkty P INNER JOIN
(SELECT TOP 1 IDProduct, Price, DateOfChange FROM sklep_CenyProduktow ORDER BY DateOfChange DESC) AS C
ON P.ID=C.IDProduct

nego78
Starting Member

5 Posts

Posted - 2004-06-14 : 04:53:22

Thank you for your help but there is a problem
it won't run on mysql because of "TOP 1"

maybe you'bve got another ideas?

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-06-14 : 05:00:37

Ah, sorry, you didn't highlight it was MySQL.

Does MAX() work on MySQL ? If so, use a correlated subquery in the derived table to determine the max date for a given product.

Never used MySQL before, so apologies if syntax not quite right !

SELECT P.ID, P.Name, P.Desc, C.Price, C.DateOfChange
FROM sklep_Produkty P INNER JOIN
(SELECT IDProduct, Price, DateOfChange
FROM sklep_CenyProduktow A
WHERE DateOfChange=(SELECT MAX(DateOfChange) FROM sklep_CenyProduktow B WHERE A.IDProduct=B.IDProduct)) AS C
ON P.ID=C.IDProduct

nego78
Starting Member

5 Posts

Posted - 2004-06-14 : 05:30:56

hi :)

many thanks
it works :))

i didn't suspect that will need have more than one subquery :)

tx again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources