CODEWITHSUNDEEP
mysql
Dave
Dave

Reputation: 193

How to select a row from MySQL when there are no rows in another table

I'm trying to select data from one table ONLY IF there is no data within the other table for the user and site.

This is what I have so far.

SELECT 
    l.link_id,
    l.link_name,
    l.link_points,
    l.link_time,
    COUNT(uc.user_id) AS clicks
FROM 
    links AS l LEFT OUTER JOIN
    (SELECT user_id, site_id FROM user_clicks WHERE site_id = l.link_id AND user_id = ".$user['user_id'].") as uc
    USING (link_id)
GROUP BY
    l.link_id

Which is my current attempt at it but I get "Unknown column 'l.link_id' in 'where clause'" when trying to pull that data in the outer join.

How do I go about this?

Upvotes: 2

Views: 477

Answers (1)

fancyPants
fancyPants

Reputation: 51878

This here should work:

SELECT 
    l.link_id,
    l.link_name,
    l.link_points,
    l.link_time,
    COUNT(uc.user_id) AS clicks
FROM 
    links AS l LEFT JOIN
    user_clicks uc ON uc.site_id = l.link_id 
WHERE user_id = ".$user['user_id']."
    AND uc.site_id IS NULL
GROUP BY
    l.link_id

The LEFT JOIN selects everything from table links and with the uc.site_id IS NULL in the WHERE clause you make sure, that there is no row in the other table. Actually it's not important which column you name there, as long as it's from user_clicks

Upvotes: 1

Related Questions