Hosting & Domaining Forum

Hosting & Domaining development => Programming Discussion => Databases => Topic started by: Ali_Pro on Jul 23, 2022, 04:21 AM

Title: MySQL. Request to join several tables, with the output of unique name.
Post by: Ali_Pro on Jul 23, 2022, 04:21 AM
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.
Title: Re: MySQL. Request to join several tables, with the output of unique name.
Post by: _AnnA_ on Jul 24, 2022, 03:26 AM
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.
Title: Re: MySQL. Request to join several tables, with the output of unique name.
Post by: Ali_Pro on Jul 24, 2022, 07:12 AM
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
Title: Re: MySQL. Request to join several tables, with the output of unique name.
Post by: waton on Oct 04, 2022, 05:05 AM
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;