Join sp problem based on invoice_id RSS

• • Reply
  

  vsp.santu

  Member

  157 Points

  425 Posts

  Join sp problem based on invoice_id

  Apr 28, 2012 07:36 AM|LINK

  Hi,

  I should create a join sp based on invoice_id. And i wrote sp like as below:

  CREATE procedure [dbo].[SP_PAYMENTINVOICEWISE_JOIN]
  @InvoiceID INT
  as
  select distinct ph.companyname,
  po.SesKey as [id], 'INV' + upper(ph.shortname)+convert(varchar(50),po.seskey) as [InvoiceID],
  ph.seskey,
  pcq.bank_name,pcq.cheque_no,pcq.cheque_date,pcq.cheque_amount,
  pcs.Amount,
  pdd.bank_name,pdd.dd_no,pdd.dd_date,pdd.dd_amount
  
  from tbl_partnerhirerege as ph
  inner join tbl_PurchageOrder as po on po.userdid = ph.seskey
  inner join tbl_PaymentUpdateCheque as pcq on pcq.invoice_id = po.seskey
  inner join tbl_PaymentUpdateCash as pcs on pcs.invoice_id = po.seskey
  inner join tbl_PaymentUpdateDD as pdd on pdd.invoice_id = po.seskey
  where po.seskey = @InvoiceID

  I should get output from above sp

  companyname InvoiceID bank_name cheque_no cheque_date cheque_amount amount  bank_name dd_no dd_date  dd_amount

     YAHOO            41          SBH         53535DFF    1/04/12              50,000        40,000      NULL      NULL    NULL          NULL

  And below are my 5 tables.

  Table1: tbl_Partnerhirerege

  seskey    companyname  shortname
    12       IBM IND LMTD     IBM
    13             Yahoo           YAH

  Table2: tbl_PurchaseOrder

  seskey   userdid     date        pono
     40          12     28/04/2012   30
     41          13     29/04/2012   40

  Table3: tbl_PaymentUpdateCheque

  seskey    invoice_id   bank_name    cheque_no     cheque_date   cheque_amount
     12           40              SBI            232323DF         22/04/12         40,000
     13           41              SBH           53535DFF          1/04/12          50,000

  Table4: tbl_PaymentUpdateCash

  seskey    invoice_id      amount
     12           41             40,000

  Table5: tbl_PaymentUpdateDD

  seskey    invoice_id      bank_name      dd_no        dd_date     dd_amount
     13           40                 SBI           3442323DF    22/04/12       40,000

  Anbody please help me. I am waiting for your reply.

  Thank you.

  • Edited by vsp.santu on Apr 28, 2012 07:37 AM
  • Edited by XIII on Apr 28, 2012 07:38 AM
  • Moved by XIII on Apr 28, 2012 07:39 AM
  • Edited by vsp.santu on Apr 28, 2012 07:41 AM
• • Reply
  

  Shankar_ss

  Participant

  1270 Points

  279 Posts

  Re: Join sp problem based on invoice_id

  Apr 28, 2012 08:26 AM|LINK

  If you need only rows from primary table, use left outer join instead of inner join.

  Hope this helps.

  Please mark as answered if this answer helped you.
  
  Shankar
  • Marked as answer by vsp.santu on Apr 29, 2012 05:29 PM
• • Reply
  

  gimimex

  Participant

  1052 Points

  157 Posts

  Re: Join sp problem based on invoice_id

  Apr 28, 2012 02:47 PM|LINK

  Hi,

  Try changing only the line below:

  left join tbl_PaymentUpdateDD as pdd on pdd.invoice_id = po.seskey

  
  Hope this helps.

  http://www.imoveisemexposicao.com.br/imoveis-em-guarulhos
• • Reply
  

  vsp.santu

  Member

  157 Points

  425 Posts

  Re: Join sp problem based on invoice_id

  Apr 29, 2012 05:29 PM|LINK

  Hi, 

  Thank you both of you. Its working