SELECTing the contents of the Table, which is a result of another value

Question

I have five tables.

Users

+--------+----------+---------------+
| UserID | Username | Password      |
+--------+----------+---------------+
| 1      | Praveen  | Praveen       |
+--------+----------+---------------+
| 2      | Stack    | StackOverflow |
+--------+----------+---------------+
| 3      | CrazyGuy | OhMyGawd!     |
+--------+----------+---------------+

Messages

+-----------+-------------+-----------+----------------------------------------------+
| MessageID | MessageFrom | MessageTo | MessageContent                               |
+-----------+-------------+-----------+----------------------------------------------+
| 1         | 1           | 2         | Hi Stack! Praveen here! :)                   |
+-----------+-------------+-----------+----------------------------------------------+
| 2         | 1           | 3         | Hey Crazy Guy, you are spamming me!!!        |
+-----------+-------------+-----------+----------------------------------------------+
| 3         | 2           | 3         | Hey, is Praveen speaking to you about spams? |
+-----------+-------------+-----------+----------------------------------------------+

Comments

+-----------+--------+----------------------------------------+
| CommentID | UserID | CommentContent                         |
+-----------+--------+----------------------------------------+
| 1         | 1      | Hello! This is Praveen! Stop spamming! |
+-----------+--------+----------------------------------------+
| 2         | 1      | Hey CrazyGuy, stop your spams!!!       |
+-----------+--------+----------------------------------------+
| 3         | 3      | SPAM! SPAM!! SPAM!!!                   |
+-----------+--------+----------------------------------------+

IndexTable

+---------+-----------+------------+---------------------+
| IndexID | IndexType | IndexRowID | IndexTime           |
+---------+-----------+------------+---------------------+
| 1       | 1         | 1          | 2015-04-10 10:50:00 |
+---------+-----------+------------+---------------------+
| 2       | 1         | 2          | 2015-04-10 10:55:00 |
+---------+-----------+------------+---------------------+
| 3       | 2         | 1          | 2015-04-10 11:25:00 |
+---------+-----------+------------+---------------------+
| 4       | 3         | 1          | 2015-04-10 11:30:00 |
+---------+-----------+------------+---------------------+
| 5       | 2         | 2          | 2015-04-10 11:45:00 |
+---------+-----------+------------+---------------------+

TableNames

+---------+-----------+
| TableID | TableName |
+---------+-----------+
| 1       | Users     |
+---------+-----------+
| 2       | Messages  |
+---------+-----------+
| 3       | Comments  |
+---------+-----------+

I am more interested in the Index table to list all the activities. So, if I give a query like this:

SELECT *, (
    SELECT `TableName` FROM `TableNames` WHERE `TableID`=`IndexType`
) AS `IndexTypeName` FROM `IndexTable` ORDER BY `IndexTime` DESC;

I would get all the contents like this:

+---------+-----------+------------+---------------------+------------+
| IndexID | IndexType | IndexRowID | IndexTime           | IndexTable |
+---------+-----------+------------+---------------------+------------+
| 5       | 2         | 2          | 2015-04-10 11:45:00 | Messages   |
+---------+-----------+------------+---------------------+------------+
| 4       | 3         | 1          | 2015-04-10 11:30:00 | Comments   |
+---------+-----------+------------+---------------------+------------+
| 3       | 2         | 1          | 2015-04-10 11:25:00 | Messages   |
+---------+-----------+------------+---------------------+------------+
| 2       | 1         | 2          | 2015-04-10 10:55:00 | Users      |
+---------+-----------+------------+---------------------+------------+
| 1       | 1         | 1          | 2015-04-10 10:50:00 | Users      |
+---------+-----------+------------+---------------------+------------+

If you see the result, the last column shows the Table Names and the concerned Primary Key (Item ID) of the table too. So, with the above result, I wanna add a column, that selects the main value from the table, with the ID specified.

In short, I would like the query to be:

SELECT *, (
    SELECT `TableName` FROM `TableNames` WHERE `TableID`=`IndexType`
) AS `IndexTypeName`, (
    SELECT {Username OR MessageContent OR CommentContent}
        FROM {`IndexTypeName`}
        WHERE {`UserID` OR `MessageID` OR `CommentID`} = `IndexRowID`
) AS `TableValue` FROM `IndexTable`
    ORDER BY `IndexTime` DESC;

Is it possible with MySQL-Server?

you could do that using a `CASE` construct – niyou Apr 21 '15 at 12:21 — niyou, Apr 21 '15 at 12:21

niyou · Accepted Answer · 2015-04-21T12:31:38.363

2

using CASE WHEN:

SELECT *, (
    SELECT `TableName` FROM `TableNames` WHERE `TableID`=`IndexType`
) AS `IndexTypeName`, 
CASE 
    WHEN IndexType=1 THEN (SELECT Username FROM Users WHERE IndexRowID=UserID) 
    WHEN IndexType=2 THEN (SELECT MessageContent FROM Messages WHERE IndexRowID=MessageID) 
    WHEN IndexType=3 THEN (SELECT CommentContent FROM Comments WHERE IndexRowID=CommentID) END TableValue
ORDER BY `IndexTime` DESC;

The better solution is to put the data from those different tables in one table and use the typeid to separate them

edited Apr 21 '15 at 12:31

answered Apr 21 '15 at 12:30

niyou

875
1
11
23

2

Or, a slight shorthand, `CASE IndexType WHEN 1 THEN ... WHEN 2 THEN ...`. – eggyal Apr 21 '15 at 12:31
Lemme try this... But then, is it no use for having the `TableNames` table then? – Praveen Kumar Purushothaman Apr 21 '15 at 17:08
1

thats right - as mentioned above you should think about youre table structure – niyou Apr 22 '15 at 05:50

SELECTing the contents of the Table, which is a result of another value

1 Answers1

Linked