subselect help - 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 2005 Forums

Transact-SQL (2005)

subselect help

Author

Topic

partitur
Starting Member

12 Posts

Posted - 2009-02-06 : 08:07:23

Hey guys! I'm quite new to sql so please bear with me here...

SELECT table1.x, table2.y, table3.z

FROM table1 INNER JOIN table2 ON (table1.x = table2.x)
LEFT JOIN table3 ON (table1.x=table3.x)

WHERE (table3.z IN (SELECT MAX(z) FROM table3 WHERE table1.x=table3.x ) OR table3.z IS NULL)

If table3 has association with table1 on the x variable, I need the max vaule of table3's varaible z.
The query works fine when table3 has associating data, but the problem is in the subselect I guess: it returns no data at all from any tables when table3 is null.

I thought that the "OR table3.z IS NULL)" - at the very end would take vare of that problem but so far it's no good.

Any ideas ?
Thanks from Stockholm.

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2009-02-06 : 08:16:36

[code]
SELECT table1.x,
table2.y,
(SELECT MAX(table3.z) FROM table3 WHERE table3.x = table1.x)
FROM table1
INNER JOIN table2 ON (table1.x = table2.x)
[/code]

partitur
Starting Member

12 Posts

Posted - 2009-02-06 : 09:08:46

Thanks a lot, it works like a charm.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-06 : 09:21:38

i prefer to use dervied tables to subqueries

SELECT	table1.x, 
	table2.y, 
        t3.z
FROM table1 
INNER JOIN table2 ON (table1.x = table2.x)
INNER JOIN (SELECT table3.x,MAX(table3.z) AS z FROM table3 GROUP BY table3.x)t3
ON t3.x = table1.x

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources