CODEWITHSUNDEEP
mysqlsql
Patrick Burns
Patrick Burns

Reputation: 1833

How i can use `WHERE` on `JOIN`?

I have this sql code

SELECT sites.id, sites.url, GROUP_CONCAT(categories.name,"|",categories.color SEPARATOR ",") AS categories
        FROM sites
        LEFT JOIN categories_data ON sites.id = categories_data.sites_id WHERE categories_data.deleted=0
        LEFT JOIN categories ON categories_data.categories_id = categories.id WHERE categories.deleted=0
        WHERE sites.deleted=0 GROUP BY sites.id'

Where return this error

Database_Exception [ 1064 ]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN categories ON categories_data.categories_id = categories.id WHERE cate' at line 4 [ SELECT sites.id, sites.url, GROUP_CONCAT(categories.name,"|",categories.color SEPARATOR ",") AS categories FROM sites LEFT JOIN categories_data ON sites.id = categories_data.sites_id WHERE categories_data.deleted=0 LEFT JOIN categories ON categories_data.categories_id = categories.id WHERE categories.deleted=0 WHERE sites.deleted=0 GROUP BY sites.id ]

So, how i can use WHERE on JOIN?

Thank.

Upvotes: 0

Views: 1439

Answers (3)

Dsmithos
Dsmithos

Reputation: 23

I think it was mentioned above, but alternatively you could specify all of these filters in the WHERE statements. This is what it might look like.

    SELECT sites.id, sites.url, 
    GROUP_CONCAT(categories.name,"|",categories.color SEPARATOR ",") AS categories

    FROM sites
     LEFT JOIN categories_data ON sites.id = categories_data.sites_id 
     LEFT JOIN categories ON categories_data.categories_id = categories.id

    WHERE 
     sites.deleted=0 
     and categories_data.deleted=0
     and categories.deleted=0

    GROUP BY sites.id'

When you've got errors, it might help to try the query in a very standard format before optimizing for speed and load. SELECT...FROM...WHERE...GROUP BY etc.

Upvotes: 0

blockhead
blockhead

Reputation: 9705

Just use AND

SELECT sites.id, sites.url, GROUP_CONCAT(categories.name,"|",categories.color SEPARATOR ",") AS categories
        FROM sites
        LEFT JOIN categories_data ON sites.id = categories_data.sites_id AND categories_data.deleted=0
        LEFT JOIN categories ON categories_data.categories_id = categories.id AND categories.deleted=0
        WHERE sites.deleted=0 GROUP BY sites.id'

Upvotes: 1

John Woo
John Woo

Reputation: 263693

you cannot add WHERE clause on the ON clause of the joint tables. Separate them with conditional operator (AND/OR),

SELECT sites.id, 
       sites.url, 
       Group_concat(categories.name, '|', categories.color SEPARATOR ',') AS 
       categories 
FROM   sites 
       LEFT JOIN categories_data 
              ON sites.id = categories_data.sites_id 
                 AND categories_data.deleted = 0                  -- <<== HERE
       LEFT JOIN categories 
              ON categories_data.categories_id = categories.id 
                 AND categories.deleted = 0                       -- <<== HERE
WHERE  sites.deleted = 0 
GROUP  BY sites.id

To further gain more knowledge about joins, kindly visit the link below:

Upvotes: 2

Related Questions