I have a table storing information about tickets. There is one record when ticket is sold and another when ticket is used. There is a column called TransType which is set to either 'sold' or 'used' to mark which one it is. There are other columns in the tables and some of them contains values when it is sales but not when it is used and vice versa. The table is actually a fact-table in a data warehouse style.
Amoung other things I am calculating time difference between sales and used so I am joining the table to itself in order to get one record for each ticket to be able to have timestamps for both event in the same record to calculate.
I need to include all sold tickets so an outer join should solve that.
First I ran this query
select x.* from factI as x left join factI as y on x.tickedId = y.tickedId where x.TransType = 'sold' and y.TransType = 'used'
When I run it the filter x.TransType = 'sold' is not working and the query return results for all records in factI, regardless of TransType. If I use inner join this works but obviously not returning tickets which haven't been used.
so I changed the query, to this one which gives me correct results.
select * from ( select * from factI where TransType = 'sold' ) as x left join ( select * from factI where TransType = 'used' ) as y on x.ticketId = y.ticketId
Why is it that the where clause in the first query isn't filtering out correctly when I use an outer (left) join?