How to get LIMIT on LEFT JOIN

Question

cI'm seeking some help with my left join with a limit.

What i'm trying to do is to loop through my users and check in another table if there's problems connected to the user.

But currently I'm getting all kinds of weird results and it does not LIMIT the result for each user and it also lists column status_link_missing = 0 even though i have told the sub query to only list status_link_missing = 1

So I'm stuck for now, help is much appreciated!

SELECT 
      a.user_id AS settings_userid
    , a.contact_interval
    , b.user_id 
    , b.notify_user 
    , b.status_host_down 
    , b.status_link_missing 
    , b.status_relnofollow 
FROM  `link_exchange_settings` a
LEFT JOIN link_exchange_links b 
  ON b.id
     = ( SELECT c.id 
         FROM link_exchange_links AS c 
         WHERE 
         b.user_id = a.user_id
         AND c.notify_user = 1 
         AND c.status_link_missing = 1 
         LIMIT 1
       )
WHERE a.allow_contact = 1
LIMIT 10

Edit

I switched SELECT b.id to c.id now and LIMIT works but now it only works for the first user

Answer 1

Try this change (no reference to b in the subquery):

SELECT 
      a.user_id AS settings_userid
    , a.contact_interval
    , b.user_id 
    , b.notify_user 
    , b.status_host_down 
    , b.status_link_missing 
    , b.status_relnofollow 
FROM  `link_exchange_settings` a
LEFT JOIN link_exchange_links b 
  ON b.id
     = ( SELECT c.id                        -- changed
         FROM link_exchange_links AS c 
         WHERE 
         c.user_id = a.user_id              -- changed
         AND c.notify_user = 1 
         AND c.status_link_missing = 1
         -- ORDER BY c.something            -- optional, recommended addition
         LIMIT 1
       )
WHERE a.allow_contact = 1
-- ORDER BY a.something_else                -- optional, recommended addition
LIMIT 10 ;

It's also good to have ORDER BY when you use LIMIT. Unless you want indeterminate results.

Answer 2

You are using "c.status_link_missing = 1" in sub-query but if you will use it in WHERE clause with "AND" you will get your desired results.

In fact you have to decide and use appropriate condition in WHERE clause of main query instead of LEFT JOIN sub-query.