CODEWITHSUNDEEP
phpmysql
Deepak
Deepak

Reputation: 6802

Select records from 2 tables and ordering them

I have 2 tables A and B with following records

--TABLE A--  
id        date          value  
1        09/01/2012    Reward  
2        09/01/2012    Purchase  
3        07/01/2012    Reward  
4        07/01/2012    Purchase

--TABLE B--  
id   id_from_a     date           value  
1       1         10/01/2012     Generated Rewrd   
2       3         08/01/2012     Generated Reward

Now I want the result to be like below 

id        date          value  
1         10/01/2012    Generated Reward  
1         09/01/2012    Reward  
2         09/01/2012    Purchase  
3         08/01/2012    Generated Reward  
3         07/01/2012    Reward  
4         07/01/2012    Purchase

I know using UNIONS will merge these 2 tables but how do i order it like i have mentioned above ?

Upvotes: 0

Views: 97

Answers (4)

Dipu Raj
Dipu Raj

Reputation: 1884

You can order the result of union, check the syntax below

(SELECT a FROM t1 WHERE a=10 AND B=1)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2)
ORDER BY a;

Also look http://dev.mysql.com/doc/refman/5.0/en/union.html

Upvotes: 1

Levi Botelho
Levi Botelho

Reputation: 25204

(SELECT id, date, value FROM Table_A
UNION ALL
SELECT id, date, value FROM Tabble_B)
ORDER BY id, date DESC

Make sure you use UNION ALL for safety's sake. UNION ALL is to UNION what SELECT DISTINCT is to SELECT... UNION ALL will display all instances of duplicated records instead of just one.

Upvotes: 2

Michael Berkowski
Michael Berkowski

Reputation: 270609

Looks like you need id from table_a and id_from_a from table_b. You can merely use id in the ORDER BY clause though:

SELECT 
 id, date, value
FROM table_a
UNION ALL
SELECT
  id_from_a, date, value
FROM table_b
ORDER BY id, date DESC

Upvotes: 2

Joe Stefanelli
Joe Stefanelli

Reputation: 135739

A UNION can have a single ORDER BY clause specified at the end which applies to the entire, combined result set. 

SELECT id, date, value
    FROM table_A
UNION
SELECT id, date, value
    FROM table_B
ORDER BY id, date DESC

If you don't need the deduplicating feature of the UNION operation, a UNION ALL will yield better performance.

SELECT id, date, value
    FROM table_A
UNION ALL
SELECT id, date, value
    FROM table_B
ORDER BY id, date DESC

Upvotes: 4

Related Questions