CODEWITHSUNDEEP
sqlsql-serversubquery
The Fluffy Robot
The Fluffy Robot

Reputation: 459

SQL - subquery returning more than 1 value

What my issue is:
I am constantly returning multiple values when I don't expect to. I am attempting to get a specific climate, determined by the state, county, and country.

What I've tried:
The code given below. I am unsure as to what is wrong with it specifically. I do know that it is returning multiple values. But why? I specify that STATE_ABBREVIATION = PROV_TERR_STATE_LOC and with the inner joins that I do, shouldn't that create rows that are similar except for their different CLIMATE_IDs?

SELECT
...<code>...
    (SELECT locations.CLIMATE_ID
        FROM REF_CLIMATE_LOCATION locations, SED_BANK_TST.dbo.STATIONS stations
        INNER JOIN REF_STATE states ON STATE_ID = states.STATE_ID
        INNER JOIN REF_COUNTY counties ON COUNTY_ID = counties.COUNTY_ID
        INNER JOIN REF_COUNTRY countries ON COUNTRY_ID = countries.COUNTRY_ID
        WHERE STATE_ABBREVIATION = PROV_TERR_STATE_LOC) AS CLIMATE_ID
...<more code>...
FROM SED_BANK_TST.dbo.STATIONS stations

I've been at this for hours, looking up different questions on SO, but I cannot figure out how to make this subquery return a single value.

Upvotes: 1

Views: 117

Answers (3)

Ferdinand Gaspar
Ferdinand Gaspar

Reputation: 2063

If you are expecting to have a single value on your sub-query, probably you need to use DISTINCT. The best way to see it is you run your sub-query separately and see the result. If you need to include other columns from the tables you used, you may do so to check what makes your result have multiple rows.

You can also use MAX() or MIN() or TOP 1 to get a single value on the sub-query but this is dependent to the logic you want to achieve for locations.CLIMATE_ID. You need to answer the question, "How is it related to the rest of the columns retrieved?"

Upvotes: 0

Russell Fox
Russell Fox

Reputation: 5435

Your JOINs show a hierarchy of locations: Country->State->County, but your WHERE clause only limits by the state abbreviation. By joining the county you'll get one record for every county in that state. You CAN limit your results by taking the TOP 1 of the results, but you need to be very careful that that's really what you want. If you're looking for a specific county, you'll need to include that in the WHERE clause. You get some control with the TOP 1 in that it will give the top 1 based on an ORDER BY clause. I.e., if you want the most recently added, use:

SELECT TOP 1 [whatever] ORDER BY [DateCreated] DESC;

For your subquery, you can do something like this:

SELECT TOP 1
    locations.CLIMATE_ID
FROM    REF_CLIMATE_LOCATION locations ,
        SED_BANK_TST.dbo.STATIONS stations
        INNER JOIN REF_STATE states ON STATE_ID = states.STATE_ID
        INNER JOIN REF_COUNTY counties ON COUNTY_ID = counties.COUNTY_ID
        INNER JOIN REF_COUNTRY countries ON COUNTRY_ID = countries.COUNTRY_ID
WHERE   STATE_ABBREVIATION = PROV_TERR_STATE_LOC

Just be sure to either add an ORDER BY at the end or be okay with it choosing the TOP 1 based on the "natural order" on the tables.

Upvotes: 0

Ronald
Ronald

Reputation: 2882

All those inner joins don't reduce the result set if the IDs you're testing exist in the REF tables. Apart from that you're doing a Cartesian product between locations and stations (which may be an old fashioned inner join because of the where clause).

You'll only get a single row if you only have a single row in the locations table that matches a single row in the stations table under the condition that STATE_ABBREVIATION = PROV_TERR_STATE_LOC

Upvotes: 1

Related Questions