Problem with inner joins.

  • Goalie35

    Say Hey Kid

    Points: 696

    I'm a relative newbie to sql server and I'm having a problem with a query that uses several inner joins.  It's a query that returns data based on all clients who have not paid their bills yet.  Basically, everything was working fine until I needed to select the amount of a clients invoice & the amount they were overdue.  What happens when I run this is 2 additional fields are returned(the "InvoiceAmt" field & the "OverdueAmt" field).  Unfortunately, all of the values within these fields' rows are listed as "NULL", which it incorrect.  They should display a value.  So it's something wrong with the portion of my statement that begins with "(select Sum..." and ends with "...AdjId desc) as OverdueAmt".

    Just in case, I've included the entire statement but in particular, it seems to be the portion of the statement that I mentioned above.

    begin

    declare @BusSrcId int

    declare @InvoiceNo int

    set @BusSrcId=2035

    select distinct tbilling.billid as InvoiceNo, tclients.clientname as ClientName, tclients.clientholdstmtind as ClientOnHoldInd, tclientcontacts.Billdate, (select Sum(IsNull(tbillingbillcodes.BBCConsFeeAmt,0))+Sum(IsNull(tbillingbillcodes.BBCServFeeAmt,0))

     From TBillingBillcodes Where tbillingbillcodes.BBCBillId=@InvoiceNo) as InvoiceAmt,

    (Select Top 1 IsNull((tadjustments.AdjBalAmt),0) From TAdjustments Where tadjustments.AdjBillID=@InvoiceNo Order By tadjustments.AdjId desc) as OverDueAmt from tbilling

    inner join tclients on tclients.clientid=tbilling.billclientid

    inner join tclientcontacts on tclientcontacts.clientcontclientid=tbilling.billclientid

    inner join tbussources on tbussources.bussrcid = tclientcontacts.clientcontbussrcid

    where tclientcontacts.ClientContBusSrcId=@BusSrcId and tbilling.billpmtstatus != 'PAID'

    end

    I'd appreciate any help you can provide on this.

    Thanks in advance.

    -goalie35

  • manoj.madhavan@honeywell.com

    Valued Member

    Points: 51

    Try running your subqueries separately in query analyzer. That should give you an idea where it is going wrong.

    Manoj

  • Caine Schneider

    Old Hand

    Points: 355

    It doesn't appear that you set @InvoiceNo, which is used in the WHERE clause of each subselect.  It probably should be tbilling.billid and not @InvoiceNo.

    Caine

  • Goalie35

    Say Hey Kid

    Points: 696

    "It doesn't appear that you set @InvoiceNo, which is used in the WHERE clause of each subselect.  It probably should be tbilling.billid and not @InvoiceNo."

     

    That was it.  I needed to change my WHERE clause to tbilling.billid.

    Stupid mistake. 

     Thanks for the help.

     

    -Ryan

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply