CODEWITHSUNDEEP
mysqlsql
mi553
mi553

Reputation: 35

SQL join and MySQL

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 ?

Upvotes: 0

Views: 101

Answers (8)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95532

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".

Upvotes: 0

khaled_webdev
khaled_webdev

Reputation: 1430

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

Upvotes: 0

bluevector
bluevector

Reputation: 3493

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

Upvotes: 3

Brian Hoover
Brian Hoover

Reputation: 7991

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

Upvotes: 2

Jay
Jay

Reputation: 435

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.

Upvotes: 0

Adam
Adam

Reputation: 26907

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

Upvotes: 2

DonCallisto
DonCallisto

Reputation: 29912

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

Upvotes: 0

Jeff Watkins
Jeff Watkins

Reputation: 6359

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

Upvotes: 0

Related Questions