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

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:

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

CodePudding user response：

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;