mysql left join

Question

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?

This is a poorly framed question. Please be specific when you create your question title. — Michael Fernando
– Michael Fernando, Commented Apr 3, 2012 at 11:27
indexcode="New" Should be a i.indexcode?? A typo Error?? and is trandate of type DATETIME or only DATE ? — Umesh Moghariya
– Umesh Moghariya, Commented Apr 3, 2012 at 11:28

Nanne · Accepted Answer · 2012-04-03 11:29:28Z

1

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"

answered Apr 3, 2012 at 11:29

Nanne

64.4k16 gold badges122 silver badges166 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1019538 Over a year ago

I past the result required and query generated Required result Date INDEXCODE Trandate Price 29-Mar-92 new 29-Mar-92 61.9 30-Mar-92 new null Null 31-Mar-92 new 31-Mar-92 61.9 Current query give the following result 29-Mar-92 new 29-Mar-92 61.9 31-Mar-92 new 31-Mar-92 61.9

Nanne Over a year ago

That's kinda unreadable? could you paste that in your question, using code tags and all?

Imre L · Accepted Answer · 2012-04-03 11:43:15Z

1

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"

answered Apr 3, 2012 at 11:43

Imre L

6,29126 silver badges32 bronze badges

2 Comments

user1019538 Over a year ago

i change the order as per your suggestion put no luck. i also add the i infront of indexcode. i paste the result required and what query is generated.

Imre L Over a year ago

Your result shows the query actually works. You have to look for not i.trandate but for p.trandate(the 3rd column) to be null. If you want to see only missing prices then add to the end of where clause: AND p.trandate is null. Cheers

Collectives™ on Stack Overflow

mysql left join

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related