If LEFT JOIN doesn't exist then ignore

Question

Not all child rows have a parent. The child rows that have a parent should only be fetched if parent.deleted='0'

If LEFT JOIN content as parent doesn't exist, how do i ignore parent.deleted='0' for that row?

SELECT child.* FROM `content` child LEFT JOIN 
content parent on parent.id=child.parentid AND child.submittype='2' WHERE child.username=? 
AND child.deleted='0' AND parent.deleted='0' 
ORDER BY child.id DESC LIMIT 12

How do i do this? I put parent.deleted='0' in the WHERE CLAUSE but this would only work if parent row existed.

So you ant to select only those lines where parentid is null or parent.deleted<>'0'? — andy, Oct 11 '14 at 09:01

score 2 · Answer 1 · answered Oct 11 '14 at 09:11

I think you are looking for something like this:

$getitem = $connectdb->prepare("SELECT child.* FROM `content` child LEFT JOIN 
content parent ON parent.id=child.parentid AND child.submittype='2' WHERE child.username=? 
AND child.deleted='0' AND (parent.deleted='0' OR parent.deleted IS NULL)
ORDER BY child.id DESC LIMIT 12");

Also your left join can just say the target table to join to like this:

$getitem = $connectdb->prepare("SELECT child.* FROM `content` child LEFT JOIN 
parent ON parent.id=child.parentid AND child.submittype='2' WHERE child.username=? 
AND child.deleted='0' AND (parent.deleted='0' OR parent.deleted IS NULL)
ORDER BY child.id DESC LIMIT 12");

Lastly as a general rule I leave the on statement for the directions of the join, so the child.submittype could also be moved to the WHERE clause:

$getitem = $connectdb->prepare("SELECT child.* FROM `content` child LEFT JOIN 
parent ON parent.id=child.parentid WHERE child.submittype='2' AND child.username=? 
AND child.deleted='0' AND (parent.deleted='0' OR parent.deleted IS NULL)
ORDER BY child.id DESC LIMIT 12");

score 1 · Accepted Answer · edited Oct 11 '14 at 09:36

1

The SQL you are looking for is

SELECT child.* 
FROM `content` child 
    LEFT JOIN 
        content parent 
 on parent.id=child.parentid AND child.submittype='2' 
WHERE child.username=? 
AND child.deleted='0' AND (parent.deleted='0' OR parent.deleted IS NULL)
ORDER BY child.id DESC LIMIT 12

edited Oct 11 '14 at 09:36

Max

6,821
3
43
59

answered Oct 11 '14 at 09:05

Moray McConnachie

26
2

score 0 · Answer 3 · edited May 23 '17 at 10:25

A LEFT JOIN will return all rows, even if parentid is NULL. The first step to solve your problem is therefore to use an INNER JOIN instead. See this question for an explanation.

Your result set will now only include rows, where a parent is actually available. To filter this result further, your WHERE clause is already there. However, for better readability I suggest to move the filter on submittype there as well. The overall result will be:

SELECT child.* FROM `content` child INNER JOIN
  content parent on parent.id=child.parentid
WHERE 
  child.username=? AND 
  child.submittype='2'AND 
  child.deleted='0' AND 
  parent.deleted='0' 
ORDER BY child.id DESC LIMIT 12

If LEFT JOIN doesn't exist then ignore

3 Answers3