Corrolated subquery - 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

General SQL Server Forums

New to SQL Server Programming

Corrolated subquery

Author

Topic

dalton33
Starting Member

2 Posts

Posted - 2009-12-03 : 10:41:47

Hi All,
I have this query:

select name, population
from country as c1
where population > (
select avg(population)
from country as c2
where c1.continent = c2.continent
)
order by c1.continent;

Is there a way to write this query without using Corrolated subquery for exemple using Inner joins.
Thank you for help.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-03 : 12:02:22

Homework question? Try this?


SELECT
	name
	, population
FROM
	country c
	JOIN (
		SELECT
			continent AS continent
			AVG(population) AS avg_pop
		FROM
			country
		)
		avgPop ON avgPop.continent = c.continent
WHERE
	c.population > avgPop.avg_pop

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

dalton33
Starting Member

2 Posts

Posted - 2009-12-04 : 04:39:31

Hi Charlie,
Yes you almost write but it is not homework, at the moment I'm doing course is SQL and the teacher who is doing this course he couldn't or he didn't want to answer to this question for me.
But your example is almost correct because it doesn't return the full list of countries but at this stage I probably will be able to fix this Thank you for your help
Regards
Dan

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-04 : 05:15:52

just change the join to a left join and change the WHERE clause to



c.population > avgPop.avg_pop
OR
avgPop.avg_pop IS NULL

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources