Complicated SQL Query Question

I am developing freelancer site and in this site users(project owners and experts) can leave feedback for each one. I am try to find count of feedbacks waitting to leave.

This query returns project's watting feedback count which are have no feedback in last 30 days, user id = 3 and have suitable status code:

SELECT COUNT(*)
FROM projects
WHERE projects.status IN (5, 10) AND projects.status_created >= DATE_SUB('2010-12-17 21:24:51', INTERVAL 30 DAY)    
AND NOT EXISTS(
    SELECT * FROM 
    feedbacks WHERE feedbacks WHERE projects.id = feedbacks.project_id AND feedbacks.from_id = '3'
)

This query is works when we have only 2 users in database otherwise for example if we change user id 3 to 99(user which have no relationship with project), query still return 1 for count but it should be return 0.

My database scheme:

PROJECTS(id, project_owner_id, project_title, ...)
FEEDBACKS(id, project_id, to_id, from_id, ....)
PROJECT_BIDS(id, project_id, bid_ow开发者_开发技巧ner_id, accepted, ...) We can use this table for find out which user's bid is accepted then accepted bid owner have right for leave feedback.

We can use project_bids.accepted field for find out which users have relationship with project. If accepted true then project's freelancer expert is this user. Also projects.project_owner_id is another column to determine relationship.

How can i fix my query ? Thank you.

Your query (as written) is looking for the number of projects that have been created in the last 30 days have have attached comments/feedback, and the person in question has commented on this project.

The first thing that stands out is that you're checking the date the projected was created, not the date of the comments/feedback. If you do this, when the project becomes more than 30 days old, no more feedbacks will count when running the query. You most likely will want to add a timestamp to the feedbacks table and check that field instead.

Also, you're doing a count of the number of projects, rather than the number of feedbacks that meet the criteria.

For you're query, I would try something like:

SELECT COUNT(feedbacks.id)  
FROM feedbacks, projects  
WHERE  
    projects.id = feedbacks.project_id AND  
    projects.status IN (5, 10) AND  
    feedbacks.timestamp >= DATE_SUB('2010-12-17 21:24:51', INTERVAL 30 DAY)  
ORDER BY projects.id  

This will find the number of feedbacks per each project (of the given status). If you want to count only the feedbacks that were given by the person who won the bid, you can add to the WHERE clause:

AND feedbacks.from in (  
    SELECT project_bids.bid_owner_id  
    FROM project_bids  
    WHERE  
        project_bids.accepted = 1 AND  
        project_bids.project_id = projects.id  
)  

Your English is a bit difficult to understand, so please clarify I misunderstood something.

Note to everyone else: I'm still trying to get used to the Mark Down system. Feel free to correct my formatting above.

NOT EXISTS(SELECT  FROM ...feedbacks.from_id = '99') 

Is always true: 99(user which have no relationship with project),

Thats why you «still return 1 »