CODEWITHSUNDEEP
mysqlsqljoin
Ben Guest
Ben Guest

Reputation: 1568

SQL conditional inner join

Say I have 2 tables.

Nav:
-----------
id INT NOT NULL,
title VARCHAR(50) NOT NULL,
target_page INT DEFAULT NULL;

Pages:
-----------
id INT NOT NULL,
url VARCHAR(200) NOT NULL,
access_level INT NOT NULL;

Notice that target_page in Nav can be NULL. If I wanted to get the url from Pages while selecting data from Nav I could use an inner join:

SELECT Nav.id, Nav.title, Pages.url FROM Nav
INNER JOIN Pages ON Pages.id = Nav.target_page

If I want to add an extra condition, so that say the current user of the system can only see pages they have access to I can add an extra condition:

SELECT Nav.id, Nav.title, Pages.url FROM Nav
INNER JOIN Pages ON Pages.id = Nav.target_page
WHERE Pages.access_level <= OurImaginaryUserLevel

The Inner Join ensures that the user does not have rows returned from Nav that are linked to records in Pages who's access_level is greater than the user's.

However, some records in Nav have a target_page of NULL. I would like to return these records even though the INNER JOIN prevents them from being returned as there are no records in Pages with an id of NULL.

Only records with a target_page of NULL are exempt from the INNER JOIN. Records with a target_page of Not NULL should only be returned if there is both the match in the Pages table and the Pages.access_level condition is met, hence I can not use a Left Join.

I apologise if my requirement is difficult to understand. Feel free to ask any more questions.

Thanks in advance

Upvotes: 0

Views: 675

Answers (1)

S.B.
S.B.

Reputation: 2969

I think you're misunderstanding LEFT JOIN - in your case, you could replace INNER JOIN with LEFT JOIN and get the very same results!

In order to achieve what you want, you actually need to use it, because you like to retrieve rows from Nav that don't have a target_page, too:

SELECT Nav.id, Nav.title, Pages.url FROM Nav
LEFT JOIN Pages ON Pages.id = Nav.target_page
WHERE Pages.access_level <= 1 OR target_page IS NULL

Demo: http://sqlfiddle.com/#!2/69ed06/1

As you can see, this will return both the nav with a level 1 page and that without any page association.

Upvotes: 3

Related Questions