CODEWITHSUNDEEP
mysqlsql
David
David

Reputation: 49

SQL query - find parent posts where no children posts exist

I'm using the following query:

SELECT 
count(tblReplies.repDate) as comReplies,
tblReplies.repDate
FROM tblReplies, tblQuestions, tblUsers
WHERE
tblQuestions.queID = tblReplies.repQuestionID 
AND tblQuestions.queCompanyID = tblUsers.uCompanyID
AND tblUsers.uID = tblReplies.repUserID 
AND tblUsers.uCompanyID = $comID 
AND tblQuestions.queID = $queID 
GROUP BY tblReplies.repID 
ORDER BY tblReplies.repDate ASC

Now, I'm trying to modify the query to find the posts in tblQuestions (queID) where there are no replies (questions that have no children in the database tblReplies). Anyone have an idea, I'm totally lost, hope there is a ninja out there :)

Thank you,

Upvotes: 0

Views: 91

Answers (2)

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171411

select q.*
from tblQuestions q 
left outer join tblReplies r on q.queID = r.repQuestionID
where r.repQuestionID is null

Upvotes: 2

Tim Schmelter
Tim Schmelter

Reputation: 460108

Use NOT EXISTS:

SELECT q.* 
FROM   tblquestions q
WHERE  NOT EXISTS (SELECT 1 
                   FROM   tblreplies r
                   WHERE  r.repquestionid = q.queid)

Upvotes: 1

Related Questions