CODEWITHSUNDEEP
mysqlsql
Michael Grenzer
Michael Grenzer

Reputation: 501

left join including where on main table

I have the following SQL (mysql):

SELECT SQL_CALC_FOUND_ROWS available.*
FROM   available
WHERE `fname` LIKE '%de%'
ORDER BY  `id` asc

I want to combine it with

LEFT JOIN cart ON available.id = cart.item_id
WHERE cart.item_id IS NULL

So that the result gives me only elements which are NOT in the cart table.

Upvotes: 0

Views: 79

Answers (2)

Himanshu
Himanshu

Reputation: 32602

Your query should be:

   SELECT SQL_CALC_FOUND_ROWS available.*
     FROM available
LEFT JOIN cart  
       ON available.id = cart.item_id
    WHERE cart.item_id IS NULL
      AND `fname` LIKE '%de%'
 ORDER BY  `id` asc

From your comment:

without the "where fname like xxx" it's no problem.. but with it

Look at Multiple WHERE conditions

Upvotes: 1

trapper
trapper

Reputation: 11993

You put your joins after your from, and then all your wheres together after that

SELECT SQL_CALC_FOUND_ROWS available.*
FROM   available
LEFT JOIN cart ON available.id = cart.item_id
WHERE `fname` LIKE '%de%' AND cart.item_id IS NULL
ORDER BY  `id` asc

Upvotes: 0

Related Questions