Query using "NOT EXISTS" more than once

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Snobbery08
Forum Newbie
Posts: 13
Joined: Sun Jan 06, 2008 10:45 am

Query using "NOT EXISTS" more than once

Post by Snobbery08 »

I have a query which im using "WHERE NOT EXIST" feature to do a comparison across three query lines although what has happened is, it has loaded the records which is a start, but its loaded all of them even if they don't fit the criteria. I'm going to assume my query logic is incorrect. I did get confused when i created it so was hoping some expert might be able to explain to me what i should do from here.

My query below is:

Code: Select all

<?php
$GetAppeals2 = mysql_query("SELECT RecordID,ReportedPlayer,FrozenBy,Reason FROM reportedusers WHERE EXISTS 
(SELECT SaidBy FROM reportsituation WHERE NOT EXISTS 
(SELECT UserID FROM Staff WHERE reportsituation.SaidBy = staff.UserID AND reportedusers.RecordID <> reportsituation.RecordID)
GROUP BY RecordID ORDER BY SaidTime ASC)") 
                    or die(mysql_error());
?>
What it's meant to do is this:
Get RecordID,ReportedPlayer,FrozenBy,Reason from reportedusers. If the following statement is found:

Where the "SaidBy" field from reportsituation table is NOT found in the "Staff" table by checking staff.UserID.
And also where RecordID "IS" found in the reportsituation table which is done by reportedusers.RecordID <> reportsituation.RecordID. As that is in the "NOT EXISTS" it creates the opposite of what its doing if you get me? :P

Try to imagine RecordID from reportedusers table as the "forum thread" then the RecordID's in the reportsituation is all the posts withing that thread. And they need the RecordID so the script knows where they are linked to. Now i need to get the "most recent" or in my case "most recent message" which is logged by "SaidTime" which is a time stamp.

Sorry if my explanation is poor im still learning mysql.. and this is getting quite complicated for me so hope some one can help me out :cry:
Post Reply