Page 1 of 1

Complex Query my attempted failed

Posted: Sun Jan 06, 2008 10:56 am
by Snobbery08
I am attempting to create a query which creates a list in a particular order. How ever i cannot get it to work and so was hoping you could help me.

Firstly let me explain what the order of the list should look like:

There is 2 tables.. one holds information about a reported player.. the other table holds messages between user and the staff member dealing with the user that was reported.
The list is then suppose to list ALL reports... in an order of priority.

If no one has made a message then they go at the top of the list. Followed by users who have posted and are awaiting to hear from a staff member. Then the last priority is the reports that the last message sent was by a staff member.
Awaiting Response
Awaiting Response
Awaiting Response
Last Response : User - 2 days ago
Last Response : User - 1 days ago
Last Response : User - 1 days ago
Last Response : Staff - 4 days ago
Last Response : Staff - 2 days ago
Last Response : Staff - 1 days ago
Please also notice each group is also individually sorted in ASC order.

My tables are as follows:

ReportedSituation:

RecordID | SaidBy(ID) | Said Time | Message |


Staff table
(for checking if user is a staff member of not for sorting):

UserID | StaffType |


SaidTime is the field i want to ASC order the list per group.

This was my attempt.. i am no expert but i gave it a go and had it half working...as it loaded a message lol But not all of them!

Hope you can help fix my mistake!

Query:



Code: Select all

$GetAppeals = mysql_query("select s.StaffType
     , r.SaidBy
     , r.SaidTime
	 , r.RecordID
  from reportsituation as r
inner
  join Staff as s
    on s.UserID = r.SaidBy
order
    by r.SaidBy = '' desc
	 , s.StaffType desc
     , r.SaidTime  desc")
or die(mysql_error());

Posted: Sun Jan 06, 2008 8:22 pm
by califdon
You specified an INNER JOIN, which is telling the database engine that you ONLY want to see records that MATCH. Try leaving out "inner".

Posted: Mon Jan 07, 2008 2:59 pm
by Snobbery08
Wait sorry about this i have done a re-structure of the database which might help make it easier as i have added a 3rd table to this.

This is my table structure:

ReportedSituation:

RecordID | SaidBy | SaidTime | Text |


Staff table:

UserID | Staff Type |


The "RecordID" is carrying the unique number of the report which is stored on a table here:

reportedusers:
ReportedPlayer | RecordID | Infomation | Reported By |


Now what it is meant to do ... is create a list in this order:

WHERE RecordID from reportedusers "is not found" in reportedsituation table
THEN WHERE RecordID from reportedusers "is found" in reportedsituation AND WHERE SaidBy is "not found" in Staff Table.. followed by ORDER BY SaidTime ASC
THEN WHERE RecordID from reportedusers "is found" in reportedsituation AND WHERE SaidBy is "found" in staff table... followed by ORDER BY SaidTime ASC


There is an added difficulty to it which is making my head hurt.. where by the "reportedsituation" table is a table for holding the messages between user and staff. So it needs to find the "most recent" row only for the RecordID which would then be holding the most recent message that was said. So i have either made this simpler or more complex..but i think this might be the better option having all 3 tables involved.

I'm pretty noobish at queries i can do simple ones but the second i get to something like this complex.. im lost.