Please help with query

[SirChick]

I have a query which i have got working but not quite working with what it is suppose to do.....

 

The idea is to create a list in order of "SaidBy" (which is a type of either "User" or "Staff") when these 2 groups are separated (Users being first) each group is sorted in ASC order.

 

How ever i need to make a third group for where the "SaidBy" is blank (in other words.. no one has posted anything yet).

 

So the order of the list when the query is working would show:

 

Awaiting Replies

Awaiting Replies

Awaiting Replies

Last Response: User 4 Days ago

Last Response: User 2 Days ago

Last Response: User 1 Days ago

Last Response: Staff 10 Days ago

Last Response: Staff 3 Days ago

Last Response: Staff 1 Day ago

 

 

The query won't load the "Awaiting Replies" reports (basically where there is no row related to the report in the reportsituation table which stores all the messages..

 

My Query:

 

<?php
$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 s.StaffType desc
     , r.SaidTime  desc")
or die(mysql_error()); 
?>

 

 

Hope you can help me get this working.

[SirChick]

I tried to see if this would work but it does not:

 

$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()); 

[Barand]

It looks as though you will need to add another table to the query

 

LEFT JOIN messages ON reportsituation.reportID = messages.reportID.

 

(I can only guess at table and column names)

 

Then the "No replies" are those where messages.reportID value IS NULL

[SirChick]

It looks as though you will need to add another table to the query

 

LEFT JOIN messages ON reportsituation.reportID = messages.reportID.

 

(I can only guess at table and column names)

 

Then the "No replies" are those where messages.reportID value IS NULL

 

 

I see what you mean... however the message table is "report situation". So would i have to load the same table twice?

[Barand]

Perhaps you just need a LEFT instead OF INNER join in current query then and check for a null value on the reportsituation columns.. Can't be definite without knowing how the tables hang together

[SirChick]

Well the tables are like this:

 

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 |