CODEWITHSUNDEEP
mysql
user892134
user892134

Reputation: 3224

If LEFT JOIN doesn't exist then ignore

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.

Upvotes: 0

Views: 362

Answers (3)

Moray McConnachie
Moray McConnachie

Reputation: 26

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

Upvotes: 1

andy
andy

Reputation: 2002

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

Upvotes: 0

Rurri
Rurri

Reputation: 106

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");

Upvotes: 2

Related Questions