"Inner Join with Where Condition"

An inner join is the most common join operation used in applications and can be regarded as the default join-type. Inner join creates a new result table by combining column values of two tables (A and B) based upon the join-predicate. The query compares each row of A with each row of B to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row. The result of the join can be defined as the outcome of first taking the Cartesian product (or Cross join) of all records in the tables (combining every record in table A with every record in table B)—then return all records which satisfy the join predicate.

SELECT 1oo.ID  , cm200.TicketNum
FROM CM00100 as 1oo Inner join CM20200 as cm200
ON 1oo.ID = cm200.ID
WHERE cm200. TicketType = 3;


Refrence:

http://msdn.microsoft.com/en-us/library/ms190014(v=sql.105).aspx

http://en.wikipedia.org/wiki/Join_(SQL)