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.
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'