Mysql – Handle 4 tables and to show 4 columns as result

join;mysql-5.7phpmyadminqueryunion

i can't figure out how to build the correct query that involves 4 tables and shows these 4 columns:

I want to show this 4 rows, each from a different table.

EDIT: Each company belongs to a Sector. And because of how the data it's submited and handle it inside the database, i can't have everything in only 2 tables. Every table has different uses.

So far, I have tried UNION/UNION ALL:

SELECT Companies.Symbol, CompanyData.strenght FROM Companies
INNER JOIN CompanyData ON Companies.CompanyID = CompanyData.CompanyID
        WHERE CompanyData.strenght = 'anything'
UNION ALL 
SELECT Sectors.SectorName, SectorTrend.Trend FROM    
SectorTrend
INNER JOIN Sectors ON Sectors.SectorID = SectorTrend.SectorID

I only get 2 columns: Symbol and Strenght

Several JOINS nesting the queries:

SELECT SectorTrend.Trend
FROM (
    SELECT Sectors.SectorName
    FROM (
        SELECT Companies.Symbol, CompanyData.strenght 
        INNER JOIN CompanyData ON Companies.CompanyID = CompanyData.CompanyID
        WHERE CompanyData.strenght = 'anyvalue'
         )
    INNER JOIN Sectors ON Sectors.SectorID = SectorTrend.SectorID
    WHERE Sectors.SectorID = 'anynumber'
     )
    INNER JOIN SectorTrend ON SectorTrend.SectorID = Sectors.SectorID

I get some sintax error on line 5 that can't figure out how to fix.

Using plain JOIN queries:

SELECT Companies.Symbol, Sectors.SectorName, CompanyData.strenght , 
SectorsTrend.Trend FROM Companies
INNER JOIN Companies ON Companies.SectorID = Sectors.SectorID
INNER JOIN CompanyData ON CompanyData.CompanyID = Companies.CompanyID
INNER JOIN SectorTrend ON SectorsTrend.SectorID = Sectors.SectorID

I get Error: Table/alias Companies is not unique. I have been playing switching the query order without any luck.

Is there anything that i'm missing or can you point me out another way to handle this query?
Both queries handled separately works fine.
Should i handle it separately, store the results in any variable and then create a view to display the result?

Thanks in advance.

UPDATE #1
Following @Kondybas suggestions i rewrited the query, and got empty result.
It was an multi column index problem and when i executed EXPLAIN SELECT… there were NULLS keys and NULL possible keys, so I added indexes in several columns with: CREATE INDEX _indexname_ ON _tablename_ (_column1_, _column2_); and now the query it's running faster.

But managed to get results only if i remove the last JOIN: JOIN SectorTrend AS st ON st.SectorID = s.SectorID and without using any WHERE condition.
So it seems the problem it's that last JOIN.

Final query is (changed and included some columns names):

EXPLAIN SELECT c.Symbol , s.SectorName , cprs.strenght , ssbpi.ColumnType FROM Companies AS c JOIN Sectors AS s ON s.SectorID = c.SectorID JOIN CompanyPriceRS AS cprs ON cprs.CompanyID = c.CompanyID JOIN SubSectorsBPIsData AS ssbpi ON ssbpi.SectorID = s.SectorID WHERE cprs.strenght = 'strong' AND c.SectorID = '16'

And EXPLAIN SELECT… result:

Best Answer

First I'll explain the mistakes/errors.

1) UNION provides the vertical merging not horizontal.

+---+---+         +---+---+     +---+---+       
| A | B |         | A | B |     | 1 | 2 |
+---+---+         +---+---+     | 3 | 4 |              +---+---+---+---+
| 1 | 2 |  UNION  | 6 | 7 |  =  | 6 | 7 |          not | 1 | 2 | 6 | 7 |
| 3 | 4 |         | 8 | 9 |     | 8 | 9 |              | 3 | 4 | 8 | 9 |
+---+---+         +---+---+     +---+---+              +---+---+---+---+

Also UNION requires both merged tables have columns of the same names/types/order. That is why you get only two columns while the columns and rows from the second table are dissapeared.

2) That "some syntax error" was the requirement that each tablesource used by query should have the name. When you have used the subquery you should give it an alias:

SELECT SectorTrend.Trend
  FROM ( SELECT Sectors.SectorName
           FROM ( SELECT Companies.Symbol, CompanyData.strenght 
                   --- some missed clause should be here
                   INNER JOIN CompanyData ON Companies.CompanyID = CompanyData.CompanyID
                   WHERE CompanyData.strenght = 'anyvalue'
                ) AS aaa --- that's what I mean
          INNER JOIN Sectors ON Sectors.SectorID = SectorTrend.SectorID
          WHERE Sectors.SectorID = 'anynumber'
      ) AS bbb --- that's what I mean
 INNER JOIN SectorTrend ON SectorTrend.SectorID = Sectors.SectorID

Anyway that query is too complicated and wrong to be used.

3) Multiple JOINs
Your mistake is that you have joined the table Companies twice (instead of Sectors) and engine can't tell one instance from another. The good practice is to assign an unique alias to each table in the query:

SELECT c.Symbol
     , s.SectorName
     , cd.strenght 
     , st.Trend 
  FROM Companies   AS c
  JOIN Sectors     AS s  ON s.SectorID   = c.SectorID
  JOIN CompanyData AS cd ON cd.CompanyID = c.CompanyID
  JOIN SectorTrend AS st ON st.SectorID  = s.SectorID

I've replaced the INNER JOINs by JOINs because (at least in the mysql dialect) they are exactly the same with ON clause. And this query is my choice for the task.

Proposed Solution #1

ALTER TABLE cities          ADD UNIQUE INDEX idx_city_state (city,state_code);
ALTER TABLE cities_extended ADD UNIQUE INDEX idx_city_state (city,state_code);
CREATE TABLE cities_new
    SELECT A.city,A.state_code
    FROM cities A LEFT JOIN cities_extended B USING (city,state_code)
    WHERE B.city IS NULL
;
INSERT INTO cities_extended (city,state_code)
SELECT city,state_code FROM cities_new;
DROP TABLE cities_new;

Explanation for Proposed Solution #1

Create Unique Indexes on (city,state_code) for both tables

ALTER TABLE cities          ADD UNIQUE INDEX idx_city_state (city,state_code);
ALTER TABLE cities_extended ADD UNIQUE INDEX idx_city_state (city,state_code);

Create Temp Table with rows from `cities` that's not in `cities_extended`

CREATE TABLE cities_new
    SELECT A.city,A.state_code
    FROM cities A LEFT JOIN cities_extended B USING (city,state_code)
    WHERE B.city IS NULL
;

Load the Temp Table into `cities`

INSERT INTO cities_extended (city,state_code)
SELECT city,state_code FROM cities_new;

Discard the Temp Table

DROP TABLE cities_new;

Proposed Solution #2

ALTER TABLE cities_extended ADD UNIQUE INDEX idx_city_state (city,state_code);
INSERT IGNORE INTO cities_extended
(city,state_code,zip,latitude,longitude,county)
SELECT city,state_code,0,0.0,0.0,'' FROM cities;

Explanation for Proposed Solution #2

INSERT IGNORE only works with unique indexes and primary keys. In your case, a unique index on city,state_code was needed for cities_extended.

After making such an index, you can insert all columns except id.

You could also include id like this

ALTER TABLE cities_extended ADD UNIQUE INDEX idx_city_state (city,state_code);
INSERT IGNORE INTO cities_extended
(id,city,state_code,zip,latitude,longitude,county)
SELECT NULL,city,state_code,0,0.0,0.0,'' FROM cities;

GIVE IT A TRY !!!

Mysql – Convert UNION query to JOIN. Is it actually needed for high performance

You use LIMIT without ORDER BY - that may return different 30 rows each time it is executed.

Other than that if you combine the two unioned queries into one join, you will have OR in WHERE which will check columns from different tables - that is probably going to be much slower than what you have now, union is good for this. You might want to use UNION ALL instead if you know that there will be no duplicates or that they are no problem to you as it does not have to check and eliminate them and can be even faster that UNION.

Best Answer

Related Solutions

Mysql – Merging tables, but ignoring duplicate columns