0

I'm trying to join two tables with the same structure if they match the primary key of a third table. Then I want to sort based on a column from the 3rd table.

Example structure:

 Table 1 - id (VARCHAR) | title (VARCHAR) | expire (TIMESTAMP)

 Table 2 - id (VARCHAR) | title (VARCHAR) | expire (TIMESTAMP)

 Table 3 - id (VARCHAR) | views (VARCHAR) | expire (TIMESTAMP)

I thought this should work but it doesn't:

 SELECT * FROM table1 as t, table3 as t3 WHERE t.id = t3.id
 UNION
 SELECT * FROM table2 as t2, table3 as t3 WHERE t2.id = t3.id
 ORDER BY table3.expire DESC

What could accomplish this?

user989990
  • 155
  • 1
  • 7
  • 14
  • Try this: http://stackoverflow.com/questions/213851/sql-query-using-order-by-in-union – John Mar 15 '12 at 01:20

4 Answers4

2

Bring the sorting column from table 3 in with a JOIN, and then sort on it.

SELECT table_1.*, table_3.expire as t3_expire
  FROM table_1
  INNER JOIN table_3 on table_1.id = table_3.id
UNION ALL
SELECT table_2.*, table_3.expire
  FROM table_2
  INNER JOIN table_3 on table_2.id = table_3.id
ORDER BY t3_expire DESC;
Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
0

once you have a union the data is not distinguishable. So you should try

 SELECT * FROM table1 as t, table3 as t3 WHERE t.id = t3.id
 UNION
 SELECT * FROM table2 as t2, table3 as t3 WHERE t2.id = t3.id
 ORDER BY expire DESC

Which is as good as what you are asking. Unless you really have something else in mind, in which case you should share a bit more.

kasavbere
  • 5,873
  • 14
  • 49
  • 72
0

You mean something like this?

SELECT 
    t.id AS t_id, t.title AS t_title, 
    t2.id AS t2_id, t2.title AS t2_title, 
    t3.expire 
FROM table3 AS t3
LEFT JOIN table1 as t ON t3.id = t.id
LEFT JOIN table2 as t2 ON t3.id = t2.id
ORDER BY t3.expire DESC
scibuff
  • 13,377
  • 2
  • 27
  • 30
0
SELECT *
FROM Table3
  LEFT JOIN Table1 ON Table3.id = Table1.id
  LEFT JOIN Table2 ON Table3.id = Table2.id
WHERE
  Table1.id IS NOT NULL AND Table2.id IS NOT NULL
ORDER BY Table3.expire DESC
John Pick
  • 5,562
  • 31
  • 31