Sql-server – Outer self join filters vs subqueries

join;sql serversql-server-2012subquery

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?

Best Answer

Your first query works as an inner join because the y.TransType = 'used' condition which uses the right table is in the where clause.

Your second query can be rewritten without derived tables by simply moving that condition to the on clause:

select x.*, y.*
from factI as x
left join factI as y on  x.tickedId = y.tickedId
                     and y.TransType = 'used'
where x.TransType = 'sold' ;

Related Solutions

Sql-server – How should row-specific metadata be created handled for an outer join view

If you're just trying to do a batched update to attach a bunch of tickets to an invoice (i.e. Invoices and tickets live in a 1:M relationship) then you have to get a list of ticket IDs into the stored procedure in the first place.

You could use a table variable as an input parameter if you have a version of SQL Server that supports these. Otherwise, you would have to encode the list of ticket IDs in some way, for example a list serialised as a string. You could encode the list as XML if you don't mind eternal damnation :)

If you're trying to add an Invoice record to a batch of tickets (i.e tickets live in a 1:0-1 relationship with invoices) then you have the same options for input.

In both cases the most efficient way to do the updates is likely to be creating a table variable with your list of invoice changes and then doing an update/insert operation joining the table variable against the main tables.

OTOH I can't think of anything more elegant than that.

If your problem is passing the list in across from the client via then you can pass in table-valued parameters in recent versions of ADO.NET. In older versions you are basically in the job of scrubbing and escaping the identifiers client side and passing the list across serialised as a string or XML (supported from .NET 2.0 and SQL Server 2005 onwards).

If you're using anything else (e.g. PHP) then you're probably going to have to drop down to the lowest common denominator - scrub the list of tickets and encode them as a string. Maybe the stored procedure can do some validation, such as checking the tickets all belong to the correct user.

Sql-server – Why is this Full Outer Join not working

You should do an isnull on both policynumbers so that you can group properly.

Since it's an outer-join, there's the possibility of one side of the join being NULL while still having data.

select sum(premium) Prem_Sum, sum(Loss) Loss_Sum, isnull(t1.policynumber, t2.policynumber)
from t1 full outer join t2 on t1.policynumber = t2.policynumber
group by isnull(t1.policynumber, t2.policynumber)

Related Question