Without using an explicit JOIN operator make multi table sub query in SQL?

Question

Without using an explicit JOIN operator ind the Per Capita Income of the Community Area which has a school Safety Score of 1.

    SELECT per_capita_income,community_area_name
    FROM CENSUS_DATA
    where community_area_name =
        (SELECT community_area_name FROM CHICAGO_PUBLIC_SCHOOLS where safety_score='1' )

response is "per_capita_income" "community_area_name" there is no value??? Thanks everyone for all response below but the result did not changed, there is no value, however i tried:

 SELECT community_area_name FROM CHICAGO_PUBLIC_SCHOOLS 
where safety_score='1'

and get the result as 'WASHINGTON PARK' and did seqond query as

SELECT per_capita_income,community_area_name FROM CENSUS_DATA 
where community_area_name = 'WASHINGTON PARK'

I've got again; per_capita_income community_area_name

I've checked below code to confirm and get all per_capita_income values inc. Washington Park

SELECT per_capita_income,community_area_name FROM CENSUS_DATA

so,finally I could not get the capita_income value. The data is in the below links

CENSUS_DATA:

https://ibm.box.com/shared/static/05c3415cbfbtfnr2fx4atenb2sd361ze.csv

CHICAGO_PUBLIC_SCHOOLS

https://ibm.box.com/shared/static/f9gjvj1gjmxxzycdhplzt01qtz0s7ew7.csv

CHICAGO_CRIME_DATA:

https://ibm.box.com/shared/static/svflyugsr9zbqy5bmowgswqemfpm1x7f.csv

Please execute `SELECT community_area_name FROM CHICAGO_PUBLIC_SCHOOLS where safety_score='1'` then pick the result and execute `SELECT per_capita_income,community_area_name FROM CENSUS_DATA where community_area_name = ''` and add the information to your question. BTW you probably want to replace the `=` operator by a `IN` operator. — FXD, Jan 30 '19 at 23:00
I'm voting to close this question as off-topic because not using a `JOIN` makes this a homework/quiz question, not a *practical* programming problem. — Damien_The_Unbeliever, Jan 31 '19 at 07:01
Thanks everyone,I think I solved , when I query small caps as below it worked: ``` SELECT per_capita_income,community_area_name FROM CENSUS_DATA where community_area_name= 'Washington Park' ``` I think I is not equal i in my language settings... — adams x, Jan 31 '19 at 10:24

score 1 · Answer 1 · edited Jun 11 '19 at 13:56

1

UPDATE CENSUS_DATA SET COMMUNITY_AREA_NAME = UPPER(COMMUNITY_AREA_NAME)  
select  CD.PER_CAPITA_INCOME  
from CENSUS_DATA CD,CHICAGO_PUBLIC_SCHOOLS CPS 
where CD.COMMUNITY_AREA_NAME = CPS.COMMUNITY_AREA_NAME 
and CPS.SAFETY_SCORE = 1

The CENSUS_DATA COMMUNITY_AREA_NAME column has values in lower case and for the CHICAGO_PUBLIC_SCHOOLS COMMUNITY_AREA_NAME column they are in upper case so it's better to update the particular column using UPDATE operation and perform query operation to get the required answer

edited Jun 11 '19 at 13:56

Amira Bedhiafi

8,088
6
24
60

answered Mar 28 '19 at 13:43

SAchu47

11
4

You should provide a brief explanation of why this works. – Grant Foster Mar 28 '19 at 13:48
Please _edit the answer_ you provided rather than using a comment to elaborate and clarify it. – chb Mar 28 '19 at 14:03

score 1 · Answer 2 · edited Jun 10 '19 at 11:49

1

try this one :

SELECT c.per_capita_income, c.community_area_name
    FROM CENSUS_DATA c, CHICAGO_PUBLIC_SCHOOLS s 
    WHERE s.COMMUNITY_AREA_NUMBER  = c.COMMUNITY_AREA_NUMBER AND s.SAFETY_SCORE = '1' ;

edited Jun 10 '19 at 11:49

Amira Bedhiafi

8,088
6
24
60

answered Jun 10 '19 at 10:24

Devraj Sahu

11
1

score 0 · Answer 3 · answered Jan 30 '19 at 23:13

0

It looks like you’re looking for an implicit join like this:

SELECT cd.per_capita_income,cd.community_area_name
FROM CENSUS_DATA as cd, CHICAGO_PUBLIC_SCHOOLS as cps
where cd.community_area_name = cps.community_area_name 
AND cps.safety_score='1'

answered Jan 30 '19 at 23:13

Nikolaus

1,859
1
10
16

score 0 · Answer 4 · answered Jan 31 '19 at 01:32

As commented by @lau, it is likely that the problem that you are seeing comes from your data.

However, let me also point out that there is an lurking issue with this condition :

WHERE community_area_name =
    (SELECT community_area_name FROM CHICAGO_PUBLIC_SCHOOLS where safety_score='1' )

If more than one one record in CHICAGO_PUBLIC_SCHOOLS exists with safety score equal to 1, the equality condition will not properly work (in most RDBMS this results in a runtime error).

This condition should rewritten, either as an IN condition :

SELECT per_capita_income,community_area_name
FROM CENSUS_DATA
WHERE community_area_name IN
    (SELECT community_area_name FROM CHICAGO_PUBLIC_SCHOOLS where safety_score='1' )

Or as a correlated subquery with an EXIST condition :

SELECT c.per_capita_income, c.community_area_name
FROM CENSUS_DATA c
WHERE EXISTS (
    SELECT 1 
    FROM CHICAGO_PUBLIC_SCHOOLS s
    WHERE s.community_area_name  = c.community_area_name AND s.safety_score = '1' 
)

score 0 · Answer 5 · answered Jan 31 '19 at 10:40

I have looked at the data and as everyone expected, I can confirm there is a problem with your data.

COMMUNITY_AREA_NAME is uppercase in CHICAGO_PUBLIC_SCHOOL It is propercase in CENSUS_DATA.

A string that is lowercase is always different than a string that is UPPERCASE and they are both different from a string that is Propercase.

The correct solution is probably to review your database design and put a foreign key somewhere. We do not have enough details to help on such a thing.

The less correct set everything to propercase for instance and re-run the corrected SELECT.

UPDATE CENSUS_DATA SET community_area_name = INITCAP(community_area_name);
UPDATE CHICAGO_PUBLIC_SCHOOLS  SET community_area_name = INITCAP(community_area_name);


SELECT per_capita_income,community_area_name
    FROM CENSUS_DATA
    where community_area_name IN
        (SELECT community_area_name FROM CHICAGO_PUBLIC_SCHOOLS where safety_score='1' )

It depends on the database settings, if it’s ci (case-insensitive) there will be no problem. — Nikolaus, Feb 03 '19 at 23:52

score 0 · Answer 6 · answered Mar 09 '19 at 21:11

0

SELECT per_capita_income FROM CENSUS_DATA WHERE upper(community_area_name) IN 
(SELECT "Community_Area_Name" FROM CHICAGO_PUBLIC_SCHOOLS WHERE "Safety_Score" = 1 )

answered Mar 09 '19 at 21:11

Dipesh Yadav

2,353
1
14
9

score 0 · Answer 7 · edited Nov 24 '19 at 13:18

when are your answer should not contain any join you must use cross product for example if you have 2 tables you must use select * from table1,table2 where condition

in your case, the answer should be

select PER_CAPITA_INCOME from school S,cencus_data C where C.COMMUNITY_AREA_NAME = S.COMMUNITY_AREA_NAME and S.SAFETY_SCORE=1

if it not works change with any other common column between these 2 tables

I hope it is helpful

score 0 · Answer 8 · edited Dec 06 '19 at 17:09

0

Option 1:

select PER_CAPITA_INCOME from CENSUS_DATA 
where UPPER(COMMUNITY_AREA_NAME) = (
    select COMMUNITY_AREA_NAME from CHICAGO_PUBLIC_SCHOOLS where SAFETY_SCORE = 1
);

Option 2:

select PER_CAPITA_INCOME from CENSUS_DATA 
where UPPER(COMMUNITY_AREA_NAME) in (
    select COMMUNITY_AREA_NAME from CHICAGO_PUBLIC_SCHOOLS where SAFETY_SCORE = 1
)

Either should work fine.

edited Dec 06 '19 at 17:09

Paul Roub

36,322
27
84
93

answered Dec 06 '19 at 17:05

SQL learner

1

score 0 · Answer 9 · answered Apr 18 '20 at 09:11

If you are doing the "Databases and SQL for Data Science" course on Coursera. The problem is in the datasets. If you take a look in the census data csv the community name is on lower case, while in the schools csv is on upper case. So this should do the trick

%sql SELECT COMMUNITY_AREA_NAME, PER_CAPITA_INCOME \ FROM CENSUS_DATA \ WHERE UCASE(COMMUNITY_AREA_NAME) = (SELECT "Community_Area_Name" FROM CHICAGO_PUBLIC_SCHOOLS WHERE "Safety_Score" = 1)

score 0 · Answer 10 · answered Jun 07 '20 at 06:29

0

%sql select community_area_name, per_capita_income from census_data where community_area_name='Washington Park';

answered Jun 07 '20 at 06:29

Vivek Pandey

1
1

score 0 · Answer 11 · answered Jun 11 '20 at 03:31

0

This worked for me.

%%sql 
select CPS.COMMUNITY_AREA_NAME, CD.PER_CAPITA_INCOME, CPS.SAFETY_SCORE
    from CENSUS_DATA CD, CHICAGO_PUBLIC_SCHOOLS CPS
where CD.COMMUNITY_AREA_NUMBER = CPS.COMMUNITY_AREA_NUMBER 
    and SAFETY_SCORE = 1;

answered Jun 11 '20 at 03:31

AndrewH

1

score 0 · Answer 12 · answered Jul 09 '20 at 04:17

0

the problem is community_area_name in Chicago schools is Upper case while in Census data is lower case so this code worked with me ,,

%sql select per_capita_income,community_area_number,community_area_name from CENUS_DATA where community_area_number in (select community_area_number from SCHOOLS where safety_score=1)

answered Jul 09 '20 at 04:17

Muhammad

1

1

Please consider adding code formatting to your answer. In edit click the {} button after selecting the part that is code – shuberman Jul 09 '20 at 04:25

score -1 · Answer 13 · edited Oct 05 '19 at 01:57

-1

%%sql select  per_capita_income from CENSUS
where community_area_number = (select community_area_number from SCHOOLS where safety_score = 1)

remove the ' from around '1'

edited Oct 05 '19 at 01:57

Trenton McKinney

56,955
33
144
158

answered Oct 05 '19 at 01:19

Marifer Contreras

1

Without using an explicit JOIN operator make multi table sub query in SQL?

13 Answers13