Geek asks and answers

MySQL Inner Join table based on column value

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

When running a query against "stats", what would be the best way to internally join with the result of the tableName column to get each data table? I thought about dynamic allocations in foreach, and then merging the results. For instance:.

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

To have statistics for all tables, you can use UNION with two or more selections, 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'
)
;

Winfred LEFT JOIN s. , . ( NULL).

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'
   )
;
+7

, ?

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

?

, , , .

..

( ), , .

+3

More articles:


All Articles