Can this sub-select be re-written as a self join ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2000)

Can this sub-select be re-written as a self join ?

Author

Topic

Jusvistin
Yak Posting Veteran

81 Posts

Posted - 2003-08-07 : 11:34:40

Hello, this is my first post in this forum.

The following query returns the countryname with the
largest area for each region.

Is there a way to accomplish this same result by joining
the table to itself ?

-----------------------------------------------------
select a.region, a.countryname, a.area
from cia a
where a.area >= all
(
select area
from cia b
where a.region = b.region
)
-----------------------------------------------------

Thanks,
Kevin

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-07 : 11:43:55

quote:
Originally posted by Jusvistin

Hello, this is my first post in this forum.

The following query returns the countryname with the
largest area for each region.

Is there a way to accomplish this same result by joining
the table to itself ?

-----------------------------------------------------
select a.region, a.countryname, a.area
from cia a
where a.area >= all
(
select area
from cia b
where a.region = b.region
)
-----------------------------------------------------

Thanks,
Kevin

First, Welcome...

Second, I'm sure

Third, ALL is not a SQL syntax

Fourth, you're basically self joing to the same table...but for what purpose.....

what does (correct syntax)


    SELECT a.region, a.countryname, a.area
      FROM cia a
     WHERE a.area 
        IN
           (
             SELECT area
               FROM cia b
              WHERE a.region = b.region
            )

Do for you...

I would imagine that is the same as

SELECT a.region, a.countryname, a.area FROM cia

No?

What is the result set you want to get?

Brett

8-)

SELECT POST=NewId()

vganesh76
Yak Posting Veteran

64 Posts

Posted - 2003-08-07 : 11:44:02

Jusvistin, I find a problem with ur query, bcos u r matching the same table with no other additional condition.
any way u can try this, But I assume that this is what ur requirement is. If not please do explain in detail.

select a.region, a.countryname, max(a.area)
from cia a
group by a.region,a.countryname

V.Ganesh
NetAssetManagement.Com
vganesh76@rediffmail.com

Enjoy working

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-08-07 : 11:46:11

This accomplishes what you want but is not exactly a self-join

select a.region, a.countryname, a.area
from cia a join
(
select region, max(area) 'area'
from cia
group by region
) b on a.region = b.region and a.area = b.area

nr
SQLTeam MVY

12543 Posts

Posted - 2003-08-07 : 11:48:36

the subquery could be
select a.region, a.countryname, a.area
from cia a
where a.area =
(
select max(area)
from cia b
where a.region = b.region
)

also
select a.region, a.countryname, a.area
from cia a
join (select region, area = max(area) from cia group by region) b
on a.region = b.region
and a.area = b.area

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-08-07 : 11:50:58

X002548 -
Nope, you've got it wrong there. ALL is valid sql syntax, so is ANY and SOME. They are rarely used though.

Jusvistin
Yak Posting Veteran

81 Posts

Posted - 2003-08-07 : 12:04:39

Hello,

The query I'm using works fine, just seems to me I should be able to accomplish the same thing with a self-join.

The CIA table has a single row for each country.

Here is the table structure:

Name - Country name
Region - Approximates a continent
Area - Area in square miles.

My query returns a result set that contains a single row for each region containing the country with the largest area.

Results look like this:
--------------------------------------------------------------------
Africa Sudan 2505810
Antarctic Region Antarctica 14000000
Arctic Region Greenland 2175600
Asia Russia 17075200
Bosnia and Herzegovina, Europe Bosnia and Herzegovina 51129
Central America and the Caribbean Nicaragua 129494
Commonwealth of Independent States Kazakhstan 2717300
Europe Turkey 780580
Middle East Saudi Arabia 1960582
North America Canada 9976140
Oceania Australia 7686850
South America Brazil 8511965
South America, Central America and the Caribbean Colombia 1138910
Southeast Asia Indonesia 1919440
World Cape Verde 4033
-------------------------------------------------------------------

Thanks,
Kevin

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-07 : 12:36:48

quote:
Originally posted by setbasedisthetruepath

X002548 -
Nope, you've got it wrong there. ALL is valid sql syntax, so is ANY and SOME. They are rarely used though.

Damn, I hate when that happens...

quote:

ALL
Compares a scalar value with a single-column set of values.

Syntax
scalar_expression { = | <> | != | > | >= | !> | < | <= | !< } ALL ( subquery )

Arguments
scalar_expression

Is any valid Microsoft® SQL Server™ expression.

{ = | <> | != | > | >= | !> | < | <= | !< }

Is a comparison operator.

subquery

Is a subquery that returns a result set of one column. The data type of the returned column must be the same data type as the data type of scalar_expression.

Is a restricted SELECT statement (the ORDER BY clause, the COMPUTE clause, and the INTO keyword are not allowed).

Return Types
Boolean

Kevin, did you see Nigels response? That's a join...

Brett

8-)

SELECT POST=NewId()

Jusvistin
Yak Posting Veteran

81 Posts

Posted - 2003-08-07 : 12:50:07

Thanks everybody,

You guys are fast, AND good.

I now know where to come with all my newbie questions.

Kevin

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-08-07 : 12:54:59

quote:
Originally posted by Jusvistin

Thanks everybody,

You guys are fast, AND good.

I now know where to come with all my newbie questions.

Kevin

Hmmm, too fast for my liking really. I wish I could have the pleasure of being the first to give an answer!

Oh well....

----------------
Shadow to Light

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources