MySQL. Request to join several tables, with the output of unique name

Started by Ali_Pro, Jul 23, 2022, 04:21 AM

Previous topic - Next topic

Ali_ProTopic starter

We have multiple tables such as table1 (table_id, table_name), table2 (table_id, table_name), table3 (table_id, table_name), etc. Our goal is to find a query that can combine all of the available tables and show the distinct names of each table along with the highest identifier assigned to it.
Ali.
  •  

_AnnA_

Greetings!

SELECT
   result.*,
   (SELECT MAX(id) FROM table1) as maxTABLE1,
   (SELECT MAX(id) FROM table2) as maxTABLE2,
   (SELECT MAX(id) FROM table3) as maxTABLE3
FROM(
   (
     SELECT
       f.table_id,
       f.table_name
     FROM table1 as f
   )
   UNION ALL
   (
     SELECT
       s.table_id,
       s.table_name
     FROM table2 as s
   )
   UNION ALL
   (
     SELECT
       l.table_id,
       l.table_name
     FROM table3 as l
   )
) as result
GROUP BY name

Don't thank.
  •  

Ali_ProTopic starter

Thank you!
Settled on this:
SELECT
*,
(SELECT MAX(table_id) FROM table_1) as max1,
(SELECT MAX(table_id) FROM table_2) as max2,
(SELECT MAX(table_id) FROM table_3) as max3,
FROM table1 JOIN table3
GROUP BY name
Ali.
  •  

waton

Using the DISTINCT operator, you can select unique data for specific columns.
For instance, different products may have the same manufacturers, and let's say we have the following table of products:

USE productsdb;
 
DROP TABLE IF EXISTS Products;
 
CREATE TABLE Products
(
    Id INT AUTO_INCREMENT PRIMARY KEY,
    ProductName VARCHAR(30) NOT NULL,
    Manufacturer VARCHAR(20) NOT NULL,
    ProductCount INT DEFAULT 0,
    Price DECIMAL NOT NULL
);
INSERT INTO Products  (ProductName, Manufacturer, ProductCount, Price)
VALUES
('iPhone X', 'Apple', 3, 7000),
('iPhone 8', 'Apple', 3, 5600),
('Galaxy S9', 'Samsung', 6, 56000),
('Galaxy S8', 'Samsung', 2, 4600),
('Honor 10', 'Huawei', 3, 2600);

Now let's use the DISTINCT operator to select unique values:

SELECT DISTINCT Manufacturer FROM Products;
  •  

Susan

To achieve the desired result of combining all available tables and showing the distinct names of each table along with the highest identifier assigned to it, you can use the following detailed SQL query:

SELECT
    table_name,
    MAX(table_id) AS highest_identifier
FROM
(
    SELECT table_id, table_name FROM table1
    UNION ALL
    SELECT table_id, table_name FROM table2
    UNION ALL
    SELECT table_id, table_name FROM table3
    -- Add more UNION ALL statements for additional tables
) combined_tables
GROUP BY table_name;


In this query, the `UNION ALL` statement is used to combine the data from multiple tables into a single result set. The subquery `(SELECT table_id, table_name FROM table1 UNION ALL SELECT table_id, table_name FROM table2 UNION ALL SELECT table_id, table_name FROM table3)` combines the data from all the specified tables.

Then, we use the `GROUP BY` clause to group the result set by the `table_name` column. This allows us to get the distinct table names.

Finally, the `MAX(table_id)` function is used to find the highest identifier for each distinct table name, and it's aliased as `highest_identifier` in the output.

By executing this SQL query, you will get a result set that shows the distinct names of each table along with the highest identifier assigned to each table.
  •