Inner Join is acting like cross join?

Question

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?

can ypu give sample recors with your desired result?

John Woo
– John Woo

2013-03-06 16:46:30 +00:00
Commented Mar 6, 2013 at 16:46 — John Woo
– John Woo, Commented Mar 6, 2013 at 16:46

Matt Busche · Accepted Answer · 2013-03-06 16:48:14Z

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

Collectives™ on Stack Overflow

Inner Join is acting like cross join?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related