mysql - How to select three table with same column name but different values

Mysql - How to select three table with same column name but different values

If you have three tables with the same column names but different values and you want to select data from all three tables in MySQL, you can use the UNION operator. The UNION operator is used to combine the results of multiple SELECT statements into a single result set.

Assuming you have three tables named table1, table2, and table3 with columns column1 and column2, here's an example query:

SELECT column1, column2 FROM table1 UNION SELECT column1, column2 FROM table2 UNION SELECT column1, column2 FROM table3;

Explanation:

  • UNION combines the results of the three SELECT statements.
  • Each SELECT statement retrieves data from a different table (table1, table2, and table3).
  • The column names and data types must be the same in all three SELECT statements.

If you want to include duplicate rows (i.e., allow duplicates in the result set), you can use UNION ALL instead of UNION. If you want to remove duplicates, use UNION.

-- Include duplicates SELECT column1, column2 FROM table1 UNION ALL SELECT column1, column2 FROM table2 UNION ALL SELECT column1, column2 FROM table3; 
-- Remove duplicates SELECT column1, column2 FROM table1 UNION SELECT column1, column2 FROM table2 UNION SELECT column1, column2 FROM table3;

Adjust the table and column names based on your actual database schema.

Examples

  1. MySQL Select Same Column from Three Tables

    • "MySQL select same column from three tables"
    • Code Implementation:
      SELECT table1.column_name, table2.column_name, table3.column_name FROM table1, table2, table3 WHERE condition;
    • Description: Selecting the same column from three tables in a basic cross-join manner with a condition.

  2. MySQL Select Same Column with Aliases

    • "MySQL select same column with aliases from three tables"
    • Code Implementation:
      SELECT table1.column_name AS col1, table2.column_name AS col2, table3.column_name AS col3 FROM table1, table2, table3 WHERE condition;
    • Description: Using aliases to differentiate the columns from each table.

  3. MySQL Select Same Column with INNER JOIN

    • "MySQL select same column with INNER JOIN from three tables"
    • Code Implementation:
      SELECT table1.column_name, table2.column_name, table3.column_name FROM table1 INNER JOIN table2 ON table1.id = table2.id INNER JOIN table3 ON table1.id = table3.id WHERE condition;
    • Description: Using INNER JOIN to select the same column from three tables with a common ID.

  4. MySQL Select Same Column with UNION

    • "MySQL select same column with UNION from three tables"
    • Code Implementation:
      SELECT column_name FROM table1 UNION SELECT column_name FROM table2 UNION SELECT column_name FROM table3;
    • Description: Using UNION to combine the results of selecting the same column from each table.

  5. MySQL Select Same Column with JOIN and DISTINCT

    • "MySQL select same column with JOIN and DISTINCT from three tables"
    • Code Implementation:
      SELECT DISTINCT table1.column_name, table2.column_name, table3.column_name FROM table1 JOIN table2 ON table1.id = table2.id JOIN table3 ON table1.id = table3.id WHERE condition;
    • Description: Using JOIN and DISTINCT to select unique values of the same column from three tables.

  6. MySQL Select Same Column with COALESCE

    • "MySQL select same column with COALESCE from three tables"
    • Code Implementation:
      SELECT COALESCE(table1.column_name, table2.column_name, table3.column_name) AS combined_column FROM table1 LEFT JOIN table2 ON table1.id = table2.id LEFT JOIN table3 ON table1.id = table3.id WHERE condition;
    • Description: Using COALESCE to select the first non-null value from the same column in three tables.

  7. MySQL Select Same Column with CONCAT

    • "MySQL select same column with CONCAT from three tables"
    • Code Implementation:
      SELECT CONCAT(table1.column_name, ' ', table2.column_name, ' ', table3.column_name) AS combined_column FROM table1 JOIN table2 ON table1.id = table2.id JOIN table3 ON table1.id = table3.id WHERE condition;
    • Description: Using CONCAT to concatenate the values of the same column from three tables.

  8. MySQL Select Same Column with CASE Statements

    • "MySQL select same column with CASE from three tables"
    • Code Implementation:
      SELECT CASE WHEN table1.id IS NOT NULL THEN table1.column_name WHEN table2.id IS NOT NULL THEN table2.column_name WHEN table3.id IS NOT NULL THEN table3.column_name END AS combined_column FROM table1 FULL JOIN table2 ON table1.id = table2.id FULL JOIN table3 ON table1.id = table3.id WHERE condition;
    • Description: Using CASE statements to select the value of the same column from the first non-null table.

  9. MySQL Select Same Column with GROUP_CONCAT

    • "MySQL select same column with GROUP_CONCAT from three tables"
    • Code Implementation:
      SELECT GROUP_CONCAT(column_name) AS combined_column FROM ( SELECT column_name FROM table1 UNION SELECT column_name FROM table2 UNION SELECT column_name FROM table3 ) AS combined_table;
    • Description: Using GROUP_CONCAT to concatenate the values of the same column from three tables.

  10. MySQL Select Same Column with OUTER APPLY

    • "MySQL select same column with OUTER APPLY from three tables"
    • Code Implementation:
      SELECT t1.column_name, t2.column_name, t3.column_name FROM table1 t1 OUTER APPLY ( SELECT column_name FROM table2 WHERE t1.id = table2.id ) t2 OUTER APPLY ( SELECT column_name FROM table3 WHERE t1.id = table3.id ) t3 WHERE condition;
    • Description: Using OUTER APPLY to select the same column from three tables, similar to a left outer join.

More Tags

streamwriter angular-promise pull-request android-actionbar mayavi startup angular-components complex-numbers key-value-observing proxy

More Programming Questions

More Mixtures and solutions Calculators

More Electronics Circuits Calculators

More Financial Calculators

More Fitness Calculators