SQL join and MySQL

Question

In my database I have these tables

products
-------------
id | title  | 
-------------
1    Cheese  
2    Milk
3    Water

etc .....

products_to_city

    -----------------------
    city_id | product_id   | 
    -----------------------
    1         1
    1         2
    2         3
    3         1
    3         1

I am using this sql query to fetch the result

SELECT
 p.id,
 p.title
 FROM products p
 LEFT JOIN products_to_city ptc ON ptc.id = 1

The problem is that the query above fetches all data from table instead of only where city is 1

What am I doing wrong ?

what you want to say by 1 in your query? the id or the field number? — khaled_webdev, May 30 '12 at 14:30

score 3 · Answer 1 · answered May 30 '12 at 14:32

You didn't tell the query what to join products and products_to_city on. Especially with the LEFT JOIN which will force a result set to include every record in products for every record in products_to_city. I think you want this:

SELECT p.id, p.title
FROM products p
INNER JOIN products_to_city ptc
  ON p.id = ptc.product_id
WHERE
  ptc.city_id = 1

score 2 · Answer 2 · answered May 30 '12 at 14:30

2

use this:

SELECT
 p.id,
 p.title
 FROM products p
 INNER JOIN products_to_city ptc ON ptc.id = p.id
 where ptc.id = 1

answered May 30 '12 at 14:30

Adam

26,549
8
62
79

score 2 · Answer 3 · answered May 30 '12 at 14:31

2

You aren't joining products to city, you are getting a match of all the products and city.

SELECT
 p.id,
 p.title
 FROM products p
 LEFT JOIN products_to_city ptc ON ptc.product_id = p.id
 where ptc.city_id = 1

answered May 30 '12 at 14:31

Brian Hoover

7,861
2
28
41

score 0 · Answer 4 · answered May 30 '12 at 14:29

0

You're doing an outer join, so where no match is made, you get the row returned anyway. Consider an inner join.

answered May 30 '12 at 14:29

Jeff Watkins

6,343
16
19

Mike Sherrill 'Cat Recall' · Answer 5 · 2012-05-30T19:55:07.120

When you do this

LEFT JOIN products_to_city ptc

you're telling MySQL (all SQL dbms, actually) to return all the rows from products, even if they don't match the join condition.

To return only those rows that match the join condition, use

INNER JOIN products_to_city ptc ON ptc.product_id = p.id AND (something else)

Note that you need to join the tables using the right column names.

I'm not sure what you want in that "something else" clause, because something like you wrote--pct.id = 1--is normally something you might express in a WHERE clause. The problem is that the products_to_city table doesn't have any column named "id".

score 0 · Answer 6 · answered May 30 '12 at 14:30

Replace LEFT JOIN with JOIN

That's because LEFT JOIN took even the rows (at the left of the join) that haven't a corrisponing key into the right table.

The JOIN tooks only the colum from "left" and "right" tables that satisfies the pk and fk match

So this will be your solution

SELECT
p.id,
p.title
FROM products p
JOIN products_to_city ptc ON ptc.id = 1

score 0 · Answer 7 · answered May 30 '12 at 14:30

0

see Basic Question : LEFT OUTER JOIN vs Left Join, (http://stackoverflow.com/questions/2809594/basic-question-left-outer-join-vs-left-join) they are the same. you want left inner join.

answered May 30 '12 at 14:30

Jay

435
3
5

score 0 · Answer 8 · answered May 30 '12 at 14:32

0

LEFT JOIN products_to_city ptc ON p.id = ptc.id

answered May 30 '12 at 14:32

khaled_webdev

1,420
15
20

missed where ptc.id = 1 like in other anwers – khaled_webdev May 30 '12 at 15:11

SQL join and MySQL

8 Answers8