CODEWITHSUNDEEP
phpmysqljoinsql-order-by
user317005
user317005

Reputation:

mysql, order-by, check if and item is included in another table first

MySQL:

table1

+--------+-------+
| itemid | title |
+--------+-------+
|   1    | title |
+--------+-------+

table2

+----+--------+------------+
| id | itemid |    time    |
+----+--------+------------+
|  1 |    1   | 1295116368 |
+----+--------+------------+
|  1 |    2   | 1295548368 |
+----+--------+------------+
|  2 |    1   | 1294079568 |
+----+--------+------------+

PHP:

$a = mysql_query("SELECT `itemid` FROM `table2` WHERE `id` = '1' ORDER BY `time` DESC")
while($b = mysql_fetch_assoc($a))
{
    echo $b['itemid'].'<br />';
}

Output:

2
1

However, what I want to do in the query above is to also check whether itemid is also included in table1, because when it is I want it to show up first, despite the fact that it might be an older post.

I hope this makes sense.

So, the output should be:

1
2

because itemid = 1, is also included in table1.

Upvotes: 1

Views: 120

Answers (3)

Sarfraz
Sarfraz

Reputation: 382696

Use:

SELECT `itemid` FROM `table2` t2 LEFT JOIN `table1` t1
ON t2.itemid = t1.itemid WHERE t2.`id` = '1' ORDER BY t1.itemid, t2.`time` DESC

Upvotes: 1

Pan Thomakos
Pan Thomakos

Reputation: 34350

You can join to table1 with a left join and then first order by table1's item_id and then by the time on table2:

  SELECT  t2.item_id
  FROM    table2 t2 LEFT JOIN table1 t1 ON t1.item_id = t2.item_id
  WHERE   t2.id = 1
  ORDER BY IFNULL(t1.item_id, 'a') ASC, t2.time DESC

NOTE: I use the IFNULL in the order by clause to cause NULL values to be ordered last.

Upvotes: 1

Sam Dufel
Sam Dufel

Reputation: 17598

I believe you should be able to join the tables and sort by the title 

SELECT itemid FROM table2
LEFT JOIN table1 USING (itemid) 
WHERE id = '1' ORDER BY title DESC, time DESC

You may have to flip the sorting on title, I don't remember if NULL values are sorted at the beginning or end.

Upvotes: 3

Related Questions