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

1

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

2

Ok never mind.

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


select tickets.admissions,tickets.Transnumber,tickets.value + IsNull(sum(tblDWBookingFee.GrossValue), 0) from tickets
left join tblDWBookingFee on tickets.Transnumber = tblDWBookingFee.Transnumber
group by tickets.admissions,tickets.Transnumber,tickets.value,tblDWBookingFee.GrossValue
3

try a left join?

1 Like
4

Yes but i needed also to manipulate the NULL values as they return a NULL (so wee need to use isnull)

5

so what would you like it to be in case it is NULL

6

No it's fine as is now.
Thanks.