CODEWITHSUNDEEP
sqloracle-databasejoin
dbwhite64
dbwhite64

Reputation: 39

Oracle SQL - left join leading to duplicates

Happy Friday to one and all!

I have three tables that I am working with and I am having a tough time with the joins. Table 1 has fields name, id, state, sales. Table 2 has state and stateID (among others). Table 3 has stateID and region (among others). What I need is the stateID and region fields in Table 1.

What I tried is 

select name, id, state, stateID, region, sales
from table 1 taba
left join table 2 tabb
on taba.state = tabb.state
left join table 3 tabc
on tabb.stateid = tabc.stateid

This took table 1 from ~16k rows to ~100k rows

I tried just my first join by itself and got the correct number of rows. How do I get rid of these duplicate rows when I do that third join?

Table 1 
Name   ID    State        Sales  
John   01    Texas       50,234  
Steve  02    Washington  39,261
Amanda 03    Ohio        67,892

Table 2 
State       StateID
Texas       TX
Washington  WA 
Ohio        OH

Table 3
StateID    Region
TX         South
WA         Northwest
OH         Midwest

I need it to look like this:

Name   ID    State        StateID   Region   Sales  
John   01    Texas        TX       South     50,234  
Steve  02    Washington   WA       Northwest 39,261
Amanda 03    Ohio         OH       Midwest   67,892

Upvotes: 0

Views: 221

Answers (2)

gui.co
gui.co

Reputation: 51

I think using this query should do the job. 

select name, id, state, stateID, region, sales
from table1 natural join table2 natural join table3

Because you tables are using the same names for attributes, you can use the natural join (It will only keep the common attributes, assuming that all states in table1 are in table2 and all stateID in table2 are in table3).

And if you need the sum of sales for each name (which is not clear from your question) :

select name, id, state, stateID, region, SUM(sales)
from table1 natural join table2 natural join table3
group by name, id, state, stateID, region

Upvotes: 0

dfundako
dfundako

Reputation: 8314

I'm assuming each state has multiple regions, but there is nothing in table 1 that indicates a region. So when you join to table 3 and there is 100 regions in that state and the only thing you are joining on is a stateID, it will return all 100 regions.

You probably need region in your first table to indicate the region AND state the sale occurred in or you can throw a DISTINCT on the query.

select DISTINCT name, id, state, stateID, region, sales
from table 1 taba
left join table 2 tabb
on taba.state = tabb.state
left join table 3 tabc
on tabb.stateid = tabc.stateid

Based on your new provided tables and data, this should give you what you want: 

select name, id, state, stateID, region, SUM(sales) AS sales
    from table1 taba
    left join table2 tabb
    on taba.state = tabb.state
    left join table3 tabc
    on tabb.stateid = tabc.stateid
GROUP BY name, id, state, stateID, region

Upvotes: 1

Related Questions