选择两个表并放置订购优先级

I have two tables, ir1_police is included messages that reported to admin. ir1_police_flag, admin can flag reporters from 1 to 2. (1 means medium 2 means low and nothing means high priority).

If someone try to report something that is not real. Admin will flag it as 1 or 2 . So I would make a list of report that shows first high priority, second medium and at last low.

I use the mysql statement but there is a problem. if there was nothing ir1_police_report nothing will be shown. or if exist only shows they are on ir_police_flags.

I have no idea to select them if no record exists on ir1_police_flags

SELECT * FROM ir1_police 
JOIN ir1_police_flags on ir1_police_flags.uid = `ir1_police.uid 
WHERE 
ir1_police.status=0 AND ir1_police.parent_id=0 
ORDER BY ir1_police.time DESC

Replace JOINwith LEFT JOIN. The former only selects rows from the tables where a match is found, whereas the latter selects all rows from the first table, even when there is no match in the other table.

Then you can add a second field to ORDER BY:

SELECT * FROM ir1_police
LEFT JOIN ir1_police_flags ON ir1_police_flags.uid = ir1_police.uid
WHERE ir1_police.status=0 AND ir1_police.parent_id=0 
ORDER BY
    ir1_police_flags.flag ASC,
    ir1_police.time DESC

Notice the LEFT JOIN produces results where all ir1_police_flags's fields are NULL where there is no match in this table. This is perfect in your case, because NULL is considered smaller than any value, as far as ORDER BY is concerned.

Your application might justify this structure, but you should ask yourself whether this flag shouldn't be just a column in the table ir1_police altogether.