CODEWITHSUNDEEP
mysql
user1019538
user1019538

Reputation: 11

mysql left join

I have two table one is index and another is the price structure as under

table: index
columns: trandate, indexcode

table: price
Columns: truncate, symbol, price

I want to know the missing price. For that I issue the query:

select i.trandate,i.indexcode,p.trandate,p.price 
  from index i 
  left join price p on i.trandate = p.trandate 
 where p.symbol='ABC' and indexcode="New"

The above query does not show the null date even though various price in missing in price table. Only reason i understand is that the index table does not have the symbol field that's why...but as per theory if you want to show all the rows of one table and only the match value of another table then use the left or right join query...please anybody can help?

Upvotes: 0

Views: 2338

Answers (2)

Imre L
Imre L

Reputation: 6249

you have to move the conditions concerning the left joined table to the left join ON part:

select i.trandate,i.indexcode,p.trandate,p.price 
  from index i 
  left join price p on i.trandate = p.trandate and p.symbol='ABC'
 where indexcode="New"

Upvotes: 1

Nanne
Nanne

Reputation: 64399

if you do this

where p.symbol='ABC' and indexcode="New"

you only get hits that conform to that. So they all must have a symbol that is 'ABC'. Now I'm not sure why you expect your NULL values to be present if you do this, but I think you mean this?

 where (p.symbol='ABC' OR p.symbol IS NULL) and indexcode="New"

Upvotes: 1

Related Questions