Preethi Jain Preethi Jain - 7 months ago 10
SQL Question

How to retrieve Data from both the tables

This is my Database structure for two tables

CREATE TABLE `futstkrprices` (
`name` varchar(50) DEFAULT NULL,
`expiry_date` varchar(25) DEFAULT NULL,
`contract_type` varchar(50) DEFAULT NULL,
`close_price` decimal(15,2) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `historical_data` (
`symbol_name` varchar(70) DEFAULT NULL,
`open_val` decimal(15,2) DEFAULT NULL,
`high_val` decimal(15,2) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO futstkrprices (name,expiry_date,contract_type,close_price)
values ('ABIRLANUVO' ,'26-MAY-2016','FUTSTK',870.65);
INSERT INTO futstkrprices (name,expiry_date,contract_type,close_price)
values ('ABIRLANUVO' ,'28-APR-2016','FUTSTK',866.40);
INSERT INTO futstkrprices (name,expiry_date,contract_type,close_price)
values ('ABIRLANUVO' ,'30-JUN-2016','FUTSTK',875.95);


INSERT INTO historical_data (symbol_name,open_val,high_val) values ('ABIRLANUVO',872.00,878.25)


Here is the sample Fiddle

http://sqlfiddle.com/#!9/1d4f20

Could you please tell me how can i retrieve data from both the tables
I have tried it as , but its not working

select futstkrprices.name , futstkrprices.expiry_date , futstkrprices.close_price , historical_data.symbol_name ,
historical_data.open_val , historical_data.high_val from futstkrprices LEFT JOIN futstkrprices
ON futstkrprices.name=historical_data.symbol_name;


The desired output i am expecting is

name expiry_date close_price symbol_name open_val high_val
ABIRLANUVO 26-MAY-2016 870.65 ABIRLANUVO 872 878.25
ABIRLANUVO 28-APR-2016 866.4 ABIRLANUVO 872 878.25
ABIRLANUVO 30-JUN-2016 875.95 ABIRLANUVO 872 878.25

Answer

Yuou have repetead two times the same table from futstkrprices LEFT JOIN futstkrprices

try this

  select 
   futstkrprices.name ,  
   futstkrprices.expiry_date ,   
   futstkrprices.close_price , 
   historical_data.symbol_name ,
   historical_data.open_val ,
   historical_data.high_val 
 from  futstkrprices  
 LEFT JOIN historical_data
   ON futstkrprices.name=historical_data.symbol_name;
Comments