Need to get values from 2 tables but one dows not always inner join

[sapator]

Hi.
2 tables . One always have a value but the second one is not always has a value.
So this will work only if vwDWTickets has a value that is also in bookingFee:

with tickets(admissions,Transnumber,value)
as 
(
select sum(T.admissions) as admissions,T.TransNumber,sum(GrossValue) from vwDWTickets T
where T.TransNumber  = 10550901
group by T.TransNumber)


select tickets.admissions,tickets.Transnumber,tickets.value + sum(tblDWBookingFee.GrossValue) from tickets
inner join tblDWBookingFee on tickets.Transnumber = tblDWBookingFee.Transnumber
group by tickets.admissions,tickets.Transnumber,tickets.value,tblDWBookingFee.GrossValue

I did this with CTE becuase tblDWBookingFee can also have more than one row so with a simple join it will not give me the correct admissions count.

So if transaction 10550901 has booking fees then no problem, if it does not then I get no rows (logical because the inner join has no joined values)

How can I ignore the join an get all the values from "tickets" (aka vwDWTickets ) regardless if tblDWBookingFee joins with values or not?

Thanks

[techgnome]

Change to a left join...

Inner join - return rows where there are matches on both sides
Left Join - return rows where there are rows on the left side, even if no matches on the right
Right Join - return rows were there are rows on the right side, even if no matches on the left
(full) outer join - return rows from both sides, matching when possible, but also when there are no matches on either side

-tg

[sapator]

Hi.
Yes but this will set the GrossValue value as NULL

admissions Transnumber (No column name)
2 8548449 NULL

[FunkyDexter]

That's happening because anything + null = null. So this:-

tickets.value + sum(tblDWBookingFee.GrossValue)

will return null if there are no rows in tblDWBookingFee, regardless of the value from tickets.

The answer is quote simple. Use an IsNull on the tblDWBookingFee bit to convert nulls to zeros:-

tickets.value + IsNull(sum(tblDWBookingFee.GrossValue), 0)

[sapator]

Ahh, those damn nulls!!!
Thanks both.