Please Help me on SQL Server Inner Joins

• ipervez784

  SSC Enthusiast

  Points: 105

  More actions

  February 28, 2019 at 6:21 pm

  #416734

  I have three Tables which I want to Join . I wrote down the Inner Join Query , But It is giving me error (Error: CustNumbr is used Multiple times  with D)
  The SQL Query I wrote down is : Please note that I want to use all 3 conditions present in WHERE A.DOCNUMBR <> B.APTODCNM
                                      AND A.DOCNUMBR <> B.APFRDCNM AND A.CURTRXAM <>0) d AND THE 2ND CONDITION WHERE d.DOCNUMBR <> C.DOCNUMBR AS WELL. . Please help me , where I have done mistake
  SELECT * FROM (
                      
                      SELECT * FROM slbReceivablesTrx A
  JOIN RM20201 B
                                      ON A.CUSTNMBR= B.CUSTNMBR
                                      WHERE A.DOCNUMBR <> B.APTODCNM
                                      AND A.DOCNUMBR <> B.APFRDCNM AND A.CURTRXAM <>0) d

  JOIN RM20101 C
                              ON C.custnmbr = d.CUSTNMBR
                              WHERE d.DOCNUMBR <> C.DOCNUMBR

• top_order

  Grasshopper

  Points: 13

  More actions

  March 1, 2019 at 12:06 am

  #2023272

  1. You can't use SELECT * in your subquery

  SELECT *
FROM slbReceivablesTrx A
    JOIN RM20201 B ON A.CUSTNMBR = B.CUSTNMBR
WHERE A.DOCNUMBR <> B.APTODCNM AND A.DOCNUMBR <> B.APFRDCNM AND A.CURTRXAM <> 0
  because column CUSTNMBR exists in slbReceivablesTrx and RM20201.
  You have to SELECT CUSTNMBR only from one table.

  2. You don't have use subquery
  SELECT *
FROM slbReceivablesTrx a
    JOIN RM20201 b ON a.CUSTNMBR = b.CUSTNMBR
    JOIN RM20101 c ON b.CUSTNMBR = c.CUSTNMBR
WHERE a.DOCNUMBR <> b.APTODCNM
    AND a.DOCNUMBR <> b.APFRDCNM
    AND a.CURTRXAM <> 0
    AND a.DOCNUMBR <> c.DOCNUMBR

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  March 1, 2019 at 2:22 am

  #2023273

  ipervez784 - Thursday, February 28, 2019 6:21 PM

  I have three Tables which I want to Join . I wrote down the Inner Join Query , But It is giving me error (Error: CustNumbr is used Multiple times  with D)
  The SQL Query I wrote down is : Please note that I want to use all 3 conditions present in WHERE A.DOCNUMBR <> B.APTODCNM
                                      AND A.DOCNUMBR <> B.APFRDCNM AND A.CURTRXAM <>0) d AND THE 2ND CONDITION WHERE d.DOCNUMBR <> C.DOCNUMBR AS WELL. . Please help me , where I have done mistake
  SELECT * FROM (
                      
                      SELECT * FROM slbReceivablesTrx A
  JOIN RM20201 B
                                      ON A.CUSTNMBR= B.CUSTNMBR
                                      WHERE A.DOCNUMBR <> B.APTODCNM
                                      AND A.DOCNUMBR <> B.APFRDCNM AND A.CURTRXAM <>0) d

  JOIN RM20101 C
                              ON C.custnmbr = d.CUSTNMBR
                              WHERE d.DOCNUMBR <> C.DOCNUMBR

  Remove  * in sub query use whatever columns you need and for CustNumbr column give different alias name  so that you won't get CustNumbr is used Multiple times  with D)  error

  Saravanan