Inner joining a table multiple times

Question

I am trying to create a derived table from a few different tables, everything works if i do single inner join, but when i add more joins i can't get the desired result. Here is what i got now:

Table structure:

First table:

+------------+------------+------+-----+---------+-------+
| Field      | Type       | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+-------+
| TAG_ID     | int(11)    | NO   | PRI | NULL    |       |
| STATUS     | int(11)    | YES  |     | NULL    |       |
| ENABLE     | int(11)    | YES  |     | NULL    |       |
| TIME_STAMP | bigint(20) | YES  |     | NULL    |       |
| VALUE      | float      | YES  |     | NULL    |       |
+------------+------------+------+-----+---------+-------+

Second table:

+--------------------+---------------------+------+-----+------------+----------------+
| Field              | Type                | Null | Key | Default    | Extra          |
+--------------------+---------------------+------+-----+------------+----------------+
| TAG_ID             | int(11)             | NO   | PRI | NULL       | auto_increment |
| TAG_DEV_ID         | int(11)             | NO   |     | NULL       |                |
| TAG_NAME           | varchar(256)        | NO   |     | NA         |                |

.....................

| TAG_TIME_STAMP     | bigint(20) unsigned | NO   |     | 0          |                |
+--------------------+---------------------+------+-----+------------+----------------+

Here is what i do now - first of all i create a view of both tables after joining them into one big table:

CREATE VIEW U1_TEMP_TEST AS (SELECT TAG_ID AS TID, VALUE FROM table_1);
CREATE VIEW U1_TEST AS (SELECT * FROM table_2 AS T2 INNER JOIN U1_TEMP_TEST AS T1 ON T2.TAG_ID = T1.TID);

Now what i need is to join the table to itself so i could get rows from 'VALUE' in different COLUMNS, i do it like this:

SELECT U1.TAG_DEV_ID, U1.TAG_ID, U1.VALUE, U2.VALUE 
FROM U1_TEST AS U1 
INNER JOIN ((SELECT * FROM U1_TEST 
        WHERE (TAG_ID = 1 OR TAG_ID = 12 OR TAG_ID = 21)) AS U2) 
        ON U1.TAG_DEV_ID = U2.TAG_DEV_ID 
WHERE (U1.TAG_ID = 4 OR U1.TAG_ID = 20 OR U1.TAG_ID = 14) 
GROUP BY U1.TAG_DEV_ID;

and i get good result like that:

+------------+--------+--------+-------+
| TAG_DEV_ID | TAG_ID |  VALUE | VALUE |
+------------+--------+--------+-------+
|          1 |      4 |   -0.5 |   2.1 |
|          2 |     14 |  -12.7 |   0.4 |
|          3 |     20 |   -5.7 |   9.5 |
+------------+--------+--------+-------+

But if i try adding one more join like that:

SELECT U1.TAG_DEV_ID, U1.TAG_ID, U1.VALUE, U2.VALUE, U3.VALUE 
FROM U1_TEST AS U1 
INNER JOIN ((SELECT * FROM U1_TEST 
    WHERE (TAG_ID = 1 OR TAG_ID = 12 OR TAG_ID = 21)) AS U2) 
    ON U1.TAG_DEV_ID = U2.TAG_DEV_ID 
INNER JOIN ((SELECT * FROM U1_TEST 
    WHERE (TAG_ID = 3 OR TAG_ID = 13 OR TAG_ID = 22)) AS U3) 
    ON U1.TAG_DEV_ID = U3.TAG_DEV_ID 
WHERE U1.TAG_ID = 4 OR U1.TAG_ID = 14 OR U1.TAG_ID = 24 
GROUP BY U1.TAG_DEV_ID;

the data returned is not what i expect, not all values are selected. Maybe there is something wrong with the second inner join, i'm stuck with this for hours and can't understand why the single join query works fine and the last one doesn't.

Thanks for any help !

EDIT result data:

+------------+--------+-------+-------+-------+
| TAG_DEV_ID | TAG_ID | VALUE | VALUE | VALUE |
+------------+--------+-------+-------+-------+
|          1 |      4 |  -0.5 |     3 |     0 |
|          2 |     14 | -12.7 |   0.5 |   1.6 |
+------------+--------+-------+-------+-------+

and i expect it to be something like that:

+------------+--------+-------+-------+-------+
| TAG_DEV_ID | TAG_ID | VALUE | VALUE | VALUE |
+------------+--------+-------+-------+-------+
|          1 |      4 |  -0.5 |     3 |     0 |
|          2 |     14 | -12.7 |   0.5 |   1.6 |
|          3 |     21 |     x |     x |     x |
+------------+--------+-------+-------+-------+

You want to use an *outer* join: learn about [SQL joins](http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html). — eggyal, Jan 14 '13 at 14:49
Have a read of this. It explains what's going on - http://stackoverflow.com/a/38578/368186 — Jim Jeffries, Jan 14 '13 at 14:52
"the data returned is not what i expect" Can you show what you got and what you expected? — Mark Byers, Jan 14 '13 at 14:55

score 0 · Accepted Answer · answered Jan 14 '13 at 15:00

0

Use LEFT JOIN like so:

SELECT 
  U1.TAG_DEV_ID, 
  U1.TAG_ID, 
  U1.VALUE, 
  U2.VALUE, 
  U3.VALUE 
FROM U1_TEST AS U1 
LEFT JOIN U1_TEST AS U2  ON U1.TAG_DEV_ID = U2.TAG_DEV_ID 
                        AND U3.TAG_ID IN(1, 12, 21) 
LEFT JOIN U1_TEST AS U3  ON U1.TAG_DEV_ID = U3.TAG_DEV_ID 
                        AND U3.TAG_ID IN(3, 13, 22) 
WHERE U1.TAG_ID IN(4, 14, 24)
GROUP BY U1.TAG_DEV_ID;

answered Jan 14 '13 at 15:00

Mahmoud Gamal

78,257
17
139
164

Thanks for the answer! Your query now returns expected 3 rows, but it results in some odd null value in the last row.. – rock-ass Jan 14 '13 at 15:07
@rock-ass - Can you please post some sample data of this view `U1_TEST`? it would be helpful. Thanks. – Mahmoud Gamal Jan 14 '13 at 15:08
Oh nevermind, it was a problem in the TAG_ID's, i gave the query correct IDs and it's working fine. Thanks a lot! – rock-ass Jan 14 '13 at 15:17
@rock-ass Your're wlecome. – Mahmoud Gamal Jan 14 '13 at 15:36

score 0 · Answer 2 · answered Jan 14 '13 at 15:19

You are using joins in the wrong way. Your where clauses only filter one of the 3 tables. Your query looks as follows:

SELECT U1.TAG_DEV_ID, U1.TAG_ID, U1.VALUE, U2.VALUE, U3.VALUE 
FROM U1_TEST AS U1 
INNER JOIN ((SELECT * FROM U1_TEST 
    WHERE (TAG_ID = 1 OR TAG_ID = 12 OR TAG_ID = 21)) AS U2) 
    ON U1.TAG_DEV_ID = U2.TAG_DEV_ID 
INNER JOIN ((SELECT * FROM U1_TEST 
    WHERE (TAG_ID = 3 OR TAG_ID = 13 OR TAG_ID = 22)) AS U3) 
    ON U1.TAG_DEV_ID = U3.TAG_DEV_ID 
WHERE U1.TAG_ID = 4 OR U1.TAG_ID = 14 OR U1.TAG_ID = 24 
GROUP BY U1.TAG_DEV_ID;

Problems:

1.) WHERE (TAG_ID = 1 OR TAG_ID = 12 OR TAG_ID = 21)) AS U2) is applicable only to U2

2.) WHERE (TAG_ID = 3 OR TAG_ID = 13 OR TAG_ID = 22)) AS U3) is applicable only to U3

As a result you expected row with Tag_ID = 21 cannot be in your results by definition, as it is part of U1 and U2, but your filter at U3 excluded the record and there is nothing in U3 to join to with Tag_ID = 21.

Instead I would use a query like:

SELECT U1.TAG_DEV_ID, U1.TAG_ID, U1.VALUE, U2.VALUE, U3.VALUE 
FROM U1_TEST AS U1 
INNER JOIN TEST AS U2 
ON U1.TAG_DEV_ID = U2.TAG_DEV_ID 
INNER JOIN U3
    ON U1.TAG_DEV_ID = U3.TAG_DEV_ID 
    WHERE (TAG_ID = 1 OR TAG_ID = 12 OR TAG_ID = 21)) AND 
    (TAG_ID = 3 OR TAG_ID = 13 OR TAG_ID = 22)) AND
    U1.TAG_ID = 4 OR U1.TAG_ID = 14 OR U1.TAG_ID = 24 
GROUP BY U1.TAG_DEV_ID;

I didn't test this code, but you get the idea.

The where clauses is intended to be that way so the those values appear in appropriate rows/columns of the result set. — rock-ass, Jan 14 '13 at 15:24
I've answered to your original question where you expected a record with TAG_ID = 21. In the meantime you've fixed your issue, namely TAG_ID values. — Lajos Arpad, Jan 14 '13 at 19:15

Inner joining a table multiple times

2 Answers2