CODEWITHSUNDEEP
mysql
Pawan
Pawan

Reputation: 32321

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

Upvotes: 4

Views: 42

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133360

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;

Upvotes: 1

Related Questions