CODEWITHSUNDEEP
phpmysqlsql-order-by
user317005
user317005

Reputation:

MySQL: How do I order the results depending on if they are also present in other tables?

MySQL (simplified tables)

table_1

+----+-------+
| id | title |
+----+-------+
| 1  | test  |
+----+-------+
| 2  | test  |
+----+-------+
| 3  | test  |
+----+-------+

table_2

+----+
| id |
+----+
| 1  |
+----+

table_3

+----+
| id |
+----+
| 1  |
+----+
| 3  |
+----+

PHP

$a = mysql_query("SELECT t1.id FROM table_1 AS t1 WHERE MATCH(t1.title) AGAINST ('test' IN BOOLEAN MODE)");

What I want to do now is:

a) If the id is included in table_2 OR table_3 it should be ranked higher than if it's only present in table_1

b) If the id is included in table_2 AND table_3 it should be ranked even higher

So, the output should be:

1, 3, 2

Upvotes: 1

Views: 90

Answers (2)

mkilmanas
mkilmanas

Reputation: 3485

SELECT a.id 
FROM table_1 a
LEFT JOIN table_2 b ON b.id = a.id
LEFT JOIN table_3 c ON c.id = a.id
ORDER BY
    b.id IS NOT NULL AND c.id IS NOT NULL DESC,
    b.id IS NOT NULL OR c.id IS NOT NULL DESC

You can ad subsequent orderings of what to do when multiple entries have the same priority

Upvotes: 1

Steve Mayne
Steve Mayne

Reputation: 22818

select table_1.id, 
  (case
  when max(table_2.id) is not null AND max(table_3.id) is not null then 0
  when max(table_2.id) is not null OR max(table_3.id) is not null then 1
  else 2
  end) rank
from table_1 left outer join table_2 on
   table_1.id = table_2.id left outer join table_3 on
   table_1.id = table_3.id
group by table_1.id
order by rank

Upvotes: 1

Related Questions