使用PDO并根据用户组获取链接

Question

Working with a CMS at the moment coded in PHP and i have encountered a small "hinderance".

I'm using a PDO(mysql) connection and i have two tables.

Navigation_links that have the following columns : id, title and pageid Navigation_links_usergroups_relations with these columns : linkid and usergroupid

First and foremost i am trying to create a query that takes ALL of the links in which are NOT represented in the relations table. This is proving quite difficult for me. I have tried something like this:

SELECT navigation_links.id ,navigation_links.title, navigation_links.pageid 
  FROM navigation_links, navigation_links_usergroups 
 WHERE navigation_links.id != navigation_links_usergroups.linkid

Which in my mind translates to "Show all Links that don't exist in the relations table" as the links that aren't there should be visible to anyone.

Any suggestions or ideas? I'm quite sure that there is a way to do it. But maybe I'm attacking the problem from the wrong angle?

Answer 1

You need to use correlation sub query, like this:

SELECT l.id, l.title, l.pageid
  FROM navigation_links AS l
 WHERE NOT EXISTS(
           SELECT lug.* 
             FROM navigation_links_usergroups AS lug
            WHERE lug.linkid = l.id
       )