Inner join not returning valid records

I have 2 table as event_master,events as

1. event_maste开发者_StackOverflow中文版r

id subject location
1  Test    Test

1. events

event_id event_master_id from_user_id to_user_id status
1                1           100          100       1
2                1           100          101       1

I write the following code to fetch records,

"SELECT *  FROM event_master as em
           INNER JOIN event as e
           ON em.Id = e.event_master_id
           WHERE em.Id = e.event_master_id AND (e.from_user_id = '100' OR e.to_user_id = 100) and e.status = 1"

It gives me 2 records, what i need is only 1 record and that will be where from_user_id and to_user_id is same, and if not then look for to_user_id

If you want the record where to and from are the same, or else the record for the to user, why not just filter for the to user:

SELECT * 
FROM 
  event_master as em 
  INNER JOIN event as e ON em.Id = e.event_master_id 
WHERE 
  em.Id = e.event_master_id AND 
  e.to_user_id = 100 and 
  e.status = 1

First of all, the em.Id = e.event_master_id in the WHERE clause is redundant, you already checked it in the ON clause!

Besides that, here's what's happening.

You say

event_master AS em INNER JOIN event AS e
ON em.id = e.event_master_id

This gives you the following table:

em.id em.subject em.location e.event_id e.event_master_id e.from_user_id e.to_user_id e.status
1     Test       Test        1          1                 100            100           1
1     Test       Test        1          1                 100            101           1

because the one line of event_master matches both lines of event.

Then you are selecting from this table on this condition:

em.Id = e.event_master_id
AND
    (e.from_user_id = '100'
    OR
    e.to_user_id = 100)
AND 
e.status = 1

Well, the first part of the AND is true for both rows, because that was your condition for INNER JOIN. The third part is also true for both rows, because e.status is 1. The second part, which is the OR part, says e.from_user_is = 100 OR blablabla. Since e.from_user_is = 100 is true for both rows, this part of the AND predicate is true too.

Therefore, you get all the three parts of the AND predicate true for both rows. As a result, SELECT returns both rows to you.

Now what you want is not really clear to me. If you want from_user_id to be equal to to_user_id, you should put ... AND e.from_user_id = e.from_user_id in your WHERE predicate.

To part where you said and if not then look for to_user_id I didn't really get.