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

There are several tables: table1 (table_id, table_name), table2 (table_id, table_name), table3 (table_id, table_name), and so on.
We need an example query that would join all available tables and display the unique names of these tables with the maximum identifier assigned to each.
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;
  •