MySQL Inner Join table based on column value

Question

Suppose I have a table 'stats' with the following structure:
tableName | id | pageViews
The tableName column corresponds to separate tables in the database.

When running a query against "stats", what would be the best way to inner join against the tableName column result to get each table's data?
I was thinking of running dynamic selects in a foreach and then merging the results. E.g.:

foreach($tableNames as $tableName) {
    $sql = "SELECT      *
            FROM        stats s
            INNER JOIN  $tableName tbl ON s.id = tbl.id
            WHERE       tableName = '$tableName'";
}

You say that "*The tableName column corresponds to separate tables in the database*". What does "id" correspond to? Do I understand correctly that this stats table has statistics about every row of every table? So every row in stats stores "pageViews" for an "id" in table "tableName"? — ypercubeᵀᴹ, Mar 28 '11 at 06:13

ypercubeᵀᴹ · Accepted Answer · 2011-03-28T13:42:27.600

To have all tables' statistics, you can use a UNION, with 2 or more selects, one for each table:

( SELECT s.*
       , table1.title AS name      --or whatever field you want to show
  FROM stats s
    JOIN $tableName1 table1
      ON s.id = table1.id
  WHERE tableName = '$tableName1'
)
UNION ALL
( SELECT s.*
       , table2.name AS name      --or whatever field you want to show
  FROM stats s
    JOIN $tableName2 table2
      ON s.id = table2.id
  WHERE tableName = '$tableName2'
)
UNION ALL
( SELECT s.*
       , table3.lastname AS name      --or whatever field you want to show
  FROM stats s
    JOIN $tableName3 table3
      ON s.id = table3.id
  WHERE tableName = '$tableName3'
)
;

Using Winfred's idea with LEFT JOINs. It produces different results, e.g. every field from the other tables is output in it's own column (and many NULLs occur).

SELECT s.*
     , table1.title      --or whatever fields you want to show
     , table2.name
     , table3.lastname   --etc
FROM stats s
  LEFT JOIN $tableName1 table1
    ON s.id = table1.id
      AND s.tableName = '$tableName1'
  LEFT JOIN $tableName2 table2
    ON s.id = table2.id
      AND s.tableName = '$tableName2'
  LEFT JOIN $tableName3 table3
    ON s.id = table3.id
      AND s.tableName = '$tableName3'
--this is to ensure that omited tables statistics don't appear
WHERE s.tablename IN
   ( '$tableName1'
   , '$tableName2'
   , '$tableName3'
   )
;

Hello. Thank you for the response. I actually ended up going with this approach before your answer but thanks again for the reassurance. — jexx2345, Mar 28 '11 at 13:28
Thnx. There is actually a way to go with Winfred's idea, using LEFT JOINs and it will produce slightly different result. — ypercubeᵀᴹ, Mar 28 '11 at 13:34

score 3 · Answer 2 · answered Mar 28 '11 at 02:20

3

Do you have the luxury to join all tables first,and process it afterwards?

SELECT *
    FROM stats s
    LEFT OUTER JOIN tbl1 ON s.id = tbl.id
    LEFT OUTER JOIN tbl2 ON s.id = tbl2.id

Then you take the value you need in your program afterwards?

You should try to minimise the number of queries you made to your database, try to do it in one go if possible.

Otherwise, think about Stored Procedures etc

This is one easy way of doing it(with overheads), Im sure others will help you out too.

answered Mar 28 '11 at 02:20

Winfred

875
6
12

Thank you for the advice. I am going to take your advice using the left outer joins. So you would say that a query with n joins is more efficient than multiple queries without using stored procedures? – jexx2345 Mar 28 '11 at 02:43
It depends on how much data you are pulling. If you are adding 1 extra column and n is relatively small, it is an acceptable solution. Otherwise, you could think about how to return all the n rows with a single query, them process the n rows internally, or move to use SPs and more advance data structure if table doesnt work for your situation. – Winfred Mar 28 '11 at 02:51
I don't think this query will show correct results. It will correlate, for example a row from tbl1 with id=1 and a row from tbl2 with same (id=1). – ypercubeᵀᴹ Mar 28 '11 at 06:20
Hello. Yea I had the same issue. I was able to get what I wanted using Union however. – jexx2345 Mar 28 '11 at 13:27

MySQL Inner Join table based on column value

2 Answers2

Linked