CODEWITHSUNDEEP
phpsqljoin
Salis
Salis

Reputation: 443

SQL - Select IDs from two different tables

I have two tables that have logging information for different actions. I want to select information from both logging tables and display it. I cannot modify the application, so I can't modify the table structure. Assuming I wanted, say, 30 records, I figured I could write some kind of join query that would just return the IDs for each table, in order by time. Then I could simply run two separate queries selecting those IDs from the individual tables. I'm not sure how to do this, or if you even can do this, and I'm kind of at a loss. Can anyone help? If there's a cleaner solution I'd be happy to hear that as well, I'm working in PHP.

Example data:

MAGICAL SELECT ? FROM table1 MAGIC JOIN table2 MAGICALLY ORDER BY date DESC LIMIT 3

Table 1

|------|-------|------------|
| id   | date  | other data |
|------|-------|------------|
| 1    | 8     | ...        |
| 2    | 5     | ...        |
| 3    | 3     | ...        |
|------|-------|------------|

Table 2

|------|-------|------------|
| id   | date  | other data |
|------|-------|------------|
| 1    | 6     | ...        |
| 2    | 4     | ...        |
| 3    | 12    | ...        |
|------|-------|------------|

Output

|-----------|-----------|
| table1_id | table2_id |
|-----------|-----------|
| NULL      | 3         |
| 1         | NULL      |
| NULL      | 1         |
|-----------|-----------|

Thanks in advance.

Upvotes: 1

Views: 6356

Answers (3)

Bill
Bill

Reputation: 4585

I guess I am reading it differently than the other answers. Sounds to me like you want a list of the top 30 IDs ordered by time, not caring which table they come from.

Select table1_id, table2_id From 
(
   Select ID as table1_id, NULL as table2_id, Date From Table1
   UNION ALL
   Select NULL as table1_id, ID as table2_id, Date From Table2 
)
Order by Date DESC 
Limit 30

Upvotes: 2

lc.
lc.

Reputation: 116478

SELECT table1_id, table2_id
FROM
(
    SELECT id AS table1_id, NULL AS table2_id, ctime AS time
    FROM Table1
    UNION ALL
    SELECT NULL AS table1_id, id AS table2_id, date AS time
    FROM Table2
) x
ORDER BY time DESC
LIMIT 3

SQL Fiddle example

Upvotes: 3

paquettg
paquettg

Reputation: 1374

SELECT table1.id AS table1_id, table2.id AS table2_id 
FROM table1 
JOIN table2 
ON table1.id = table2.id 
ORDER BY table2.date DESC

Upvotes: 0

Related Questions