CODEWITHSUNDEEP
sqlsql-serverjoin
valter.vx
valter.vx

Reputation: 137

Inner Join is acting like cross join?

I'm trying to create a query creating joins between several tables. Here is an example:

select b.name as state, c.name as city, sum(qnt) as total_quantity 
from qtn_table a, state_table b, city_table c
where a.state_id = b.id and a.city_id = c.id
and a.year = 2011 and a.product = 1 group by b.name, c.name

Also tried with Inner Join:

select b.name as state, c.name as city, sum(qnt) as total_quantity 
from qtn_table a
inner join state_table b ON a.state_id = b.id
inner join city_table c ON a.city_id = c.id
where a.year = 2011 and a.product = 1 group by b.name, c.name

And the result is the same.

It was supposed to return a city only with its own state:

state    city    total_quantity
NY       A
NY       B
Texas    C
Texas    D
Cali     E
Cali     F

But it is returning strange results such as:

state    city     total_quantity
NY       A
Texas    A
Cali     A
NY       B
...
...

On a typical cross join I believe that it should appear city A on all states, but it is only shoing on some and not all of them, which is an even stranger situation.

What am I doing wrong?

Upvotes: 1

Views: 1108

Answers (1)

Matt Busche
Matt Busche

Reputation: 14333

you're missing a join from state_table to city_table and it's returning a row for each state in that table or for each state that has a city with the same name (appears to be at least). I added in AND state_table.state = city_table.state to your query

select b.name as state, c.name as city, sum(qnt) as total_quantity 
from qtn_table a
 inner join state_table b ON a.state_id = b.id
 inner join city_table c ON a.city_id = c.id AND state_table.state = city_table.state
where a.year = 2011 
and a.product = 1 
group by b.name, c.name

Upvotes: 3

Related Questions