Solved

Need help doing a SQL JOIN of 2 tables using MySQL

Posted on 2013-05-26
2
217 Views
Last Modified: 2013-05-27
I'm not a big SQL user and am having trouble with a join statement.
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`)
) 

Open in new window


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`)
);

Open in new window


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.Custom_Index_Name = 'Oil Stocks'
where daily_index_weightings.Trade_Date = full_daily_data.trade_date
where daily_index_weightings.symbol = full_daily_data.symbol
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;

Open in new window


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!
0
Comment
Question by:cashonly
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39198266
Looks fine to me, you have joined the 2 tables using a primary key:
(PRIMARY KEY (`Symbol`,`Trade_Date`)

I don't know the data, but as you are using an inner join this means there HAS to be a match between the 2 tables. This is a good join type to use so don't change it unless there is a need (and such a need would only arise if you can have a record in one of those tables that does not exist in the other table).

I would suggest a small change to you date range filtering, instead of less than and equal on the upper date boundary, use less than and move that date up 1 day, like this:

WHERE full_daily_data.trade_date >= '2010-01-01'
      AND full_daily_data.trade_date < '2013-01-01' --<< one day up from '2012/12/31'
      AND daily_index_weightings.custom_index_name = 'Oil-and-Gas-Driller-Explorer-Stocks'

>>Does it matter if the date for my where clause comes from one table vs. the other?
in this case no as those dates are part of the inner join it would not really make any diffrence

If the data is HUGE, then you may want to compare execution plans of some alternatives:
/* alt 1 */
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
		AND daily_index_weightings.custom_index_name = 'Oil-and-Gas-Driller-Explorer-Stocks'
WHERE full_daily_data.trade_date >= '2010-01-01'
	AND full_daily_data.trade_date < '2013-01-01' -- << one day up from '2012/12/31'
ORDER BY full_daily_data.trade_date
	, daily_index_weightings.idx_rank_for_date;

/* alt 2 */    
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 daily_index_weightings
INNER JOIN full_daily_data
	ON daily_index_weightings.Symbol = full_daily_data_1.Symbol
		AND daily_index_weightings.Trade_Date = full_daily_data.Trade_Date
WHERE daily_index_weightings.custom_index_name = 'Oil-and-Gas-Driller-Explorer-Stocks'
	AND daily_index_weightings.Trade_Date >= '2010-01-01'
	AND daily_index_weightings.Trade_Date < '2013-01-01' -- << one day up from '2012/12/31'
ORDER BY full_daily_data.trade_date
	, daily_index_weightings.idx_rank_for_date;

Open in new window

0
 

Author Closing Comment

by:cashonly
ID: 39199078
Awesome stuff PortletPaul!

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

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating and Managing Databases with phpMyAdmin in cPanel.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question