Left join a query with a cross apply

Question

Not really sure whether I can left join the results that I get from a cross apply function:

select iv.invoiceno ,w.warehouse ,iv.invoicedate ,iv.invoicedesc ,iv.status ,iv.billingstart as [BillingFrom] ,iv.billingend as [BillingTo] ,CAST((iv.invoicesubtotal) as NUMERIC(38,2))as [Sub-Total]  ,CAST((((iv.invoicesubtotal+iv.invoicetax)-iv.invoicetotal)) as NUMERIC(38,2)) as [Discount] ,CAST((iv.invoicetax) as NUMERIC(38,2)) as [SalesTax] ,CAST((iv.invoicetotal) as NUMERIC(38,2)) as [Total] ,d.deal ,d.dealno ,ivt.orderno  ,ivt.rectype     ,ivt.rectypedisplay                                 ,RTRIM(ivt.masterno) as [ICode]                                ,ivt.description as [ICodeDesc]                             ,ivt.fromdate as [From]                                 ,ivt.todate as [To]                                   ,CAST((ivt.days ) as NUMERIC(38,2)) as [days]                                   ,CAST(ivt.qty as NUMERIC(38,0)) as [qty]                                   ,CAST((ivt.cost) as NUMERIC(38,2)) as [UnitCost]                                    ,CAST((ivt.rate) as NUMERIC(38,2)) as [rate]                                       ,CAST((ivt.daysinwk)as NUMERIC(38,2)) as [D/W]                               ,CAST((ivt.discountamt)as NUMERIC(38,2)) as [Discount]                              ,CAST((ivt.extended)as NUMERIC(38,2)) as [extended]                                ,(CASE WHEN ivt.taxable='T' then 'YES' else 'NO' END)as [Taxable] ,ivt.category ,(CASE WHEN (ivt.cost > 0 and ivt.rectype='R') THEN CAST((ivt.revenuebase) as NUMERIC (38,2)) ELSE 0 END) as [subrevenue]  from invoice iv inner join deal d                                   on d.dealid=iv.dealid inner join invoiceitemview ivt                      on iv.invoiceid=ivt.invoiceid and iv.invoiceno=ivt.invoiceno inner join warehouse w                              on w.locationid=iv.locationid and w.inactive<>'T' left join category c                                on c.categoryid=ivt.categoryid  left join ordernoteview n                           on ivt.orderid=n.orderid and n.billing ='T'  where iv.locationid='00009V5H' and iv.invoiceno='H513369' and iv.status in ('CLOSED', 'PROCESSED') and iv.nocharge<>'T'         order by iv.invoiceno, iv.invoicedate,c.category,ivt.masterno 

I wanted to add a left join with this query:

select  tot.gldate, tot.glno, tot.glacctdesc,                      tot.debit,tot.credit,tot.glaccountid                       from invoice ivt cross apply dbo.funcglforinvoice(ivt.invoiceid, null, null) as tot where ivt.invoiceno='H513369' 

but when I do that it gives me way more records then there are supposed to be.

This has been executing for a while. Basically the inner cross apply query generates 204 items and I wanted that to be left join with the items from the main query; but I am doing something wrong not sure what exactly. Help will be appreciated.

Answer 1

Use OUTER APPLY. Also I'm not sure if the ON clause after the OUTER APPLY is really needed. If the invoiceid is the same coming out as going in then probably not.

Select iv.invoiceno, iv.invoiceitem,iv.invoiceno    from invoice iv inner join deal d         on d.dealid=iv.dealid inner join invoiceitemview ivt         on iv.invoiceid=ivt.invoiceid and iv.invoiceno=ivt.invoiceno inner join warehouse w         on w.locationid=iv.locationid and w.inactive<>'T' left join category c         on c.categoryid=ivt.categoryid  left join ordernoteview n         on ivt.orderid=n.orderid and n.billing ='T' OUTER APPLY dbo.funcglforinvoice(iv.invoiceid, null, null) as tot