cashonly
asked on
Need help doing a SQL JOIN of 2 tables using MySQL
I'm not a big SQL user and am having trouble with a join statement.
I have these two tables:
And:
The results I'm trying to get are rows:
full_daily_data.Trade_Date
full_daily_data.Day_Open
full_daily_data.Day_Close
full_daily_data.symbol
daily_index_weightings.idx _rank_for_ date
where daily_index_weightings.Cus tom_Index_ Name = 'Oil Stocks'
where daily_index_weightings.Tra de_Date = full_daily_data.trade_date
where daily_index_weightings.sym bol = full_daily_data.symbol
where Trade_Date >= '2010/01/01' and Trade_Date <= '2012/01/31'
This is what I have now:
It works, but I'm only using it on a small sample set so far. The amount of data I plan to use it on is HUGE.
Is this the best/most efficient way to use it? I'm not opposed to using or adding indices if that will help fetch time (insert time is not a big issue).
WHY does it work? I'm not really sure, a lot of it was guessing and I was shocked that it did actually work. Does it matter if the date for my where clause comes from one table vs. the other?
Any help with these questions would be appreciated!
I have these two tables:
CREATE TABLE `daily_index_weightings` (
`idindex_weightings` int(11) DEFAULT NULL,
`Custom_Index_Name` varchar(75) NOT NULL,
`symbol` varchar(9) NOT NULL,
`trade_date` date NOT NULL,
`mktcap_weighting` float DEFAULT NULL,
`volprice_weighting` float DEFAULT NULL,
`combo_weighting` float DEFAULT NULL,
`idx_rank_for_date` float DEFAULT NULL,
PRIMARY KEY (`Custom_Index_Name`,`symbol`,`trade_date`),
UNIQUE KEY `idx_name_sym_date_key` (`Custom_Index_Name`,`symbol`,`trade_date`),
KEY `idx_name_symbol` (`Custom_Index_Name`,`symbol`),
KEY `TradeDate` (`trade_date`)
)
And:
CREATE TABLE `full_daily_data` (
`Symbol` varchar(9) NOT NULL,
`Trade_Date` date NOT NULL DEFAULT '0000-00-00',
`Day_Open` float DEFAULT NULL,
`Day_High` float DEFAULT NULL,
`Day_Low` float DEFAULT NULL,
`Day_Close` float DEFAULT NULL,
PRIMARY KEY (`Symbol`,`Trade_Date`),
KEY `TradeDate` (`Trade_Date`)
);
The results I'm trying to get are rows:
full_daily_data.Trade_Date
full_daily_data.Day_Open
full_daily_data.Day_Close
full_daily_data.symbol
daily_index_weightings.idx
where daily_index_weightings.Cus
where daily_index_weightings.Tra
where daily_index_weightings.sym
where Trade_Date >= '2010/01/01' and Trade_Date <= '2012/01/31'
This is what I have now:
SELECT
full_daily_data.Trade_Date,
full_daily_data.Day_Open,
full_daily_data.Day_Close,
full_daily_data.Symbol,
daily_index_weightings.idx_rank_for_date
FROM full_daily_data
INNER JOIN daily_index_weightings ON daily_index_weightings.Symbol = full_daily_data_1.Symbol
AND daily_index_weightings.Trade_Date = full_daily_data.Trade_Date
WHERE full_daily_data.trade_date >= '2010/01/01' AND full_daily_data.trade_date <= '2012/12/31'
AND daily_index_weightings.custom_index_name = 'Oil-and-Gas-Driller-Explorer-Stocks'
ORDER BY full_daily_data.trade_date, daily_index_weightings.idx_rank_for_date;
It works, but I'm only using it on a small sample set so far. The amount of data I plan to use it on is HUGE.
Is this the best/most efficient way to use it? I'm not opposed to using or adding indices if that will help fetch time (insert time is not a big issue).
WHY does it work? I'm not really sure, a lot of it was guessing and I was shocked that it did actually work. Does it matter if the date for my where clause comes from one table vs. the other?
Any help with these questions would be appreciated!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Just the kind of answer I was looking for!
I will use your code and run some tests when I get more data loaded in my DB.