How do I add a column from a different table, to the results of an existing query in sql?

BradSparks 0
Viewed 19

I have two tables CHICAGO_CRIME_DATA and CENSUS_DATA.

I am running the following query:

select count(id) as NUM_OF_CRIMES, COMMUNITY_AREA_NUMBER
from CHICAGO_CRIME_DATA
group by COMMUNITY_AREA_NUMBER
order by NUM_OF_CRIMES desc
limit 1;

to return a result with two columns:

  • NUM_OF_CRIMES
  • COMMUNITY_AREA_NUMBER

with the respective values:

  • 43
  • 25

I now want to add a column to that result called COMMUNITY_AREA_NAME from CENSUS_DATA where the COMMUNITY_AREA_NUMBER = 25.

The column COMMUNITY_AREA_NUMBER is in both of the tables.

I am very new to sql and have tried various implementations with sub-queries and implicit joins using aliases but cannot figure out how to do this, any help would be greatly appreciated!

Thanks

Sample data from CENSUS_DATA

Sample data from CHICAGO_CRIME_DATA

multiple-tables sql
1 Answers

You can run a sub select where you use the COMMUNITY_AREA_NUMBER of CHICAGO_CRIME_DATA to link both tables

select count(id) as NUM_OF_CRIMES, COMMUNITY_AREA_NUMBER
,( SELECT COMMUNITY_AREA_NAME 
from CENSUS_DATA where COMMUNITY_AREA_NUMBER = CHICAGO_CRIME_DATA.COMMUNITY_AREA_NUMBER)  as COMMUNITY_AREA_NAME 
from CHICAGO_CRIME_DATA 
group by COMMUNITY_AREA_NUMBER
order by NUM_OF_CRIMES desc
limit 1;

Or you can join the tables

select count(id) as NUM_OF_CRIMES, CHICAGO_CRIME_DATA.COMMUNITY_AREA_NUMBER
, MAX( COMMUNITY_AREA_NAME)  as COMMUNITY_AREA_NAME 
from CHICAGO_CRIME_DATA INNEr JOIN CENSUS_DATA ON CENSUS_DATA.COMMUNITY_AREA_NUMBER = CHICAGO_CRIME_DATA.COMMUNITY_AREA_NUMBER
group by CHICAGO_CRIME_DATA.COMMUNITY_AREA_NUMBER
order by NUM_OF_CRIMES desc
limit 1;
nbk0
Related