CODEWITHSUNDEEP
sqlt-sql
harlan
harlan

Reputation: 29

Avoiding duplication when joining on a column with duplicate values

I'm trying to join on a table column that has multiple duplicate results in the column I'm trying to join on. An example looks kinda like:

Month  | code | crime  
-----------------------
2017-1 | EO99 | Burglary
2017-1 | EO99 | Shoplifting
2017-2 | FO01 | Anti-Social Behaviour

The table that's being joined looks like:

Contents | F4  
-----------------------
EO99     | 4581  
EO98     | 10255 
FO01     | 4898

And the desired result is:

Month  | code | crime                  | Population
---------------------------------------|------------
2017-1 | EO99 | Burglary               | 4581
2017-1 | EO99 | Shoplifting            | 4581
2017-2 | FO01 | Anti-Social Behaviour  | 4898

I'm trying to join on that code column to add a fourth column that has population data. Here's my current query:

DROP TABLE LSOA_crimes_2017_joined;
SELECT DISTINCT
    crimes_data_GM_2017_2018.*, 
    ['2017_LSOA_Pop.'].F4 AS Population
INTO 
    LSOA_crimes_2017_joined
FROM 
    crimes_data_GM_2017_2018 AS data,
    ['2017_LSOA_Pop.']
INNER JOIN 
    crimes_data_GM_2017_2018 ON
    crimes_data_GM_2017_2018.[LSOA code] = 
    ['2017_LSOA_Pop.'].[Contents]
WHERE 
    crimes_data_GM_2017_2018.Month LIKE '2017%';

Using Distinct helped to avoid the crazy duplicates it was making for every result, but because some of the results are exactly the same yet different instances of crime, I need both to be in the new table.

Upvotes: 0

Views: 206

Answers (1)

Jayvee
Jayvee

Reputation: 10875

You had both tables in the from clause and that was creating the cartesian product. You may need to do something like this:

DROP TABLE LSOA_crimes_2017_joined;
SELECT DISTINCT
    crimes_data_GM_2017_2018.*, 
    ['2017_LSOA_Pop.'].F4 AS Population
INTO 
    LSOA_crimes_2017_joined
FROM 
    crimes_data_GM_2017_2018 AS data
INNER JOIN 
    ['2017_LSOA_Pop.'] ON
    crimes_data_GM_2017_2018.[LSOA code] = 
    ['2017_LSOA_Pop.'].[Contents]
WHERE 
    crimes_data_GM_2017_2018.Month LIKE '2017%';

Upvotes: 1

Related Questions