1

i can't figure out how to build the correct query that involves 4 tables and shows these 4 columns: My 4 Tables 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: enter image description here

0

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.

  • Your answer makes a lot of sense, it's pretty helpful. Thanks a lot for making my mind clear. However I still have to dig a bit more on this since phpmyadmin it's taking a long time to execute the query and even though it has not errors, it's showing a empty result. I'm not using any conditions and i know the tables has values. Question: Is it possible to add a WHERE condition that involves another column in any of these tables? or should I include that column in the SELECT as well? For instance: `WHERE c.SectorID = '16 AND st.Trend = 'value'; Thanks again. – gallo2000sv Feb 5 at 16:47
  • @gallo2000sv The fetched columns can be arbitrary columns not used by the ON/GROUP/WHERE/ORDER BY clauses. The long time of execution means that you have no proper indexes for that query. EXPLAIN SELECT... shows you the execution plan used for the query. I suspect you'll get a lot of NULLs in the possible keys and key columns. The general rule - you need an (multicolumn) index having all the columns used for ON/GROUP/WHERE/ORDER BY clauses. The empty result can be caused by the exact data you have stored in the tables. Try to disable the joins from bottom to top to see some more. – Kondybas Feb 5 at 17:51
  • wow! that was awesome. Didn't remember about indexes. When i did EXPLAIN SELECT... there were NULLS keys and 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, also managed to get results only if i remove the last JOIN: JOIN SectorTrend AS st ON st.SectorID = s.SectorID So it seems the problem it's there, i'm checking/adding the indexes and also testing different WHERE conditions including the primary key. Hope i could find the problem. Thanks a lot mate! – gallo2000sv Feb 5 at 23:42
  • I have updated the question with the final query and EXPLAIN SELECT... result to keep anyone informed :) – gallo2000sv Feb 6 at 0:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.