CODEWITHSUNDEEP
sqldb2
Alessio Baldessarro
Alessio Baldessarro

Reputation: 3

SQL exercise - implicit join

Good afternoon everyone,

I'm a beginner, trying to solve an exercise and can't understand why as result I get nothing but the headers.

I'm using IBM Db2.

Consider 2 tables:

CHICAGO_PUBLIC_SCHOOLS: (COMMUNITY_AREA_NAME | SAFETY_SCORE)

CENSUS_DATA: (COMMUNITY_AREA_NAME | PER_CAPITA_INCOME)

Exercise: [Without using an explicit JOIN operator] Find the Per Capita Income of the Community Area which has a Safety Score of 1

my code:

SELECT COMMUNITY_AREA_NAME, PER_CAPITA_INCOME 

FROM CENSUS_DATA 

where COMMUNITY_AREA_NAME IN
            (SELECT  COMMUNITY_AREA_NAME FROM CHICAGO_PUBLIC_SCHOOLS
                WHERE SAFETY_SCORE=1)

Thanks a lot to whoever will help me understanding

EDIT: A little add on based on the first two comments I received, hoping this might be helpful. the safety score shouldn't be an issue, since the following code works properly:

SELECT COMMUNITY_AREA_NAME FROM CHICAGO_PUBLIC_SCHOOLS WHERE SAFETY_SCORE=1

Upvotes: 0

Views: 87

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

First, you should qualify all column names in any query that has more than one table reference:

SELECT cd.COMMUNITY_AREA_NAME, cd.PER_CAPITA_INCOME 
FROM CENSUS_DATA cd
WHERE cd.COMMUNITY_AREA_NAME IN
            (SELECT cps.COMMUNITY_AREA_NAME
             FROM CHICAGO_PUBLIC_SCHOOLS cps
             WHERE cps.SAFETY_SCORE = 1
            );

Here are possibilities that I can readily think of to explain why this query would return no rows:

  1. Either table has no rows.
  2. CHICAGO_PUBLIC_SCHOOLS has no rows that match SAFETY_SCORE = 1.
  3. Any rows with SAFETY_SCORE = 1 have area names that are not in the census table.
  4. COMMUNITY_AREA_NAME does not match between the two tables.

You have not provided sample data or a fiddle. Here are some ideas for debugging:

For the first possibility, run COUNT(*) on the two tables.

For the second, just run the subquery to see if it returns any rows.

For the third, run the subquery and manually check to see if the names match in the table.

For the fourth, you have to consider strings that look the same but are different -- often because of hidden characters.

Upvotes: 1

Related Questions