mssql and left join duplicate records

119

February 07, 2019, at 05:50 AM

Original Query

SELECT DISTINCT
      IP.op_code as ip_op_code,
      IPH.op_code as iph_op_code,
      debt_trans.tx_amount as cash,
      DT.tx_amount as revenue
    FROM debt_trans
    LEFT JOIN debt_trans DT ON DT.debt_code=debt_trans.debt_code
    LEFT JOIN instplan IP ON IP.debt_code=debt_trans.debt_code
    LEFT JOIN instplanheader IPH ON IPH.debt_code=debt_trans.debt_code
    AND debt_trans.tran_code NOT IN ('DR3001','DR3002','DR3003','DR3004','RP1800','CC5000')
    AND debt_trans.tx_amount > 0
    AND debt_trans.tx_date >= '2019-02-04' AND debt_trans.tx_date <= '2019-02-04'
    AND IP.ipactualpaymentamt > 0
    AND IP.tran_code NOT IN ('DR3001','DR3002','DR3003','DR3004','RP1800','CC5000')
    AND IP.ipactualpaymentdt >= '2019-02-04' AND IP.ipactualpaymentdt <= '2019-02-04'
    AND (IP.ipactualpaymentdt+debt_trans.tx_time)=(debt_trans.tx_date+debt_trans.tx_time)
    AND DT.tran_code IN ('CC5000')
    AND DT.tx_amount > 0.00
    AND DT.tx_date >= '2019-02-04' AND DT.tx_date <= '2019-02-04'
    AND DT.tx_date=debt_trans.tx_date
    AND DT.tx_time=debt_trans.tx_time
    AND IPH.ipplanid=IP.ipplanid

Output Results

Row Count : 4
[0] => Array
    (
        [ip_op_code] => DOMP
        [iph_op_code] => DOMP
        [cash] => 5.00
        [revenue] => 2.25
    )
[1] => Array
    (
        [ip_op_code] => DOMP
        [iph_op_code] => DOMP
        [cash] => 671.00
        [revenue] => 301.95
    )
[2] => Array
    (
        [ip_op_code] => RHYSL
        [iph_op_code] => RHYSL
        [cash] => 5.00
        [revenue] => 2.25
    )
[3] => Array
    (
        [ip_op_code] => RHYSL
        [iph_op_code] => RHYSL
        [cash] => 671.00
        [revenue] => 301.95
    )

Expected Results

Row Count : 2

[0] => Array
    (
        [ip_op_code] => DOMP
        [iph_op_code] => DOMP
        [cash] => 5.00
        [revenue] => 2.25
    )
[1] => Array
    (
        [ip_op_code] => RHYSL
        [iph_op_code] => RHYSL
        [cash] => 671.00
        [revenue] => 301.95
    )

i have now added the full proper query im trying to do please could you try help me on this one.

i have 3 tables.

debt_trans instplan instplanheader

and i have to join debt_trans in again as i have to get the next row.

it all seems to go to pot when i join instplan and instplanheader

Answer 1

looking to your data could you join must match also for op_code AND instplanheader.op_code = instplan.op_code

$sql = "SELECT DISTINCT
      instplan.debt_code,
      instplan.op_code,
      instplanheader.op_code as plan_op,
      instplan.ipactualpaymentamt
    FROM instplanheader
    LEFT JOIN instplan ON instplanheader.debt_code=instplan.debt_code
        AND instplanheader.op_code = instplan.op_code 
        AND instplan.tran_code NOT IN ('DR3001','DR3002','DR3003','DR3004')
        AND instplan.ipactualpaymentamt > 0.00
        AND instplan.ipactualpaymentdt >= '2019-02-04' AND instplan.ipactualpaymentdt <= '2019-02-04'
          AND instplanheader.iphcreationdate >= '2018-12-01' AND instplanheader.iphcreationdate <= '2019-02-04'
    ";

Answer 2

It means that your right table instplan has multiple rows with the same debt_code.

Let me show what I mean:

DECLARE @TestTable TABLE 
(
    Col1 VARCHAR(10),
    Col2 INT,
    Col3 INT
)    
DECLARE @TestTable2 TABLE 
(
    Col1 VARCHAR(10),
    Col2 VARCHAR(10),
    Col3 VARCHAR(10)
)

INSERT INTO @TestTable
(
    Col1,
    Col2,
    Col3
)
VALUES
 ('A',         10,       20)    
 INSERT INTO @TestTable2
(
    Col1,
    Col2,
    Col3
)
VALUES
   ('A',         'A',       'A')
 , ('A',         'B',       'B')
 , ('A',         'C',       'C')

And query example:

SELECT 
  distinct t1.Col1 t1_Col
, t2.Col1 t2_Col1
, t2.Col2 t2_Col2
, t2.Col3 t2_Col3
FROM @TestTable t1 
LEFT JOIN @TestTable2 t2 ON t2.Col1 = t1.Col1

Nevertheless, we see three rows althought we use DISTINCT keyword.

OUTPUT:

t1_Col  t2_Col1 t2_Col2 t2_Col3
A          A       A       A 
A          A       B       B
A          A       C       C

Answer 3

This seems a touch nonsensical to me:

        AND i.ipactualpaymentdt >= '2019-02-04' 
        AND i.ipactualpaymentdt <= '2019-02-04'

POPULAR ONLINE

Too much binding in javascript with prototypes and callbacks

How to transition fill colors when your svg is in “content: url(..)”

Add custom class from link to drupal-modal drupal 8 with bootstrap theme

Issue with servers behaving differently on WordPress

SVG path dynamics value on starting point and ending point

getting SQL query for a multiselect menus

i have a multi select drop down on my site where user can select multiple categories and the search will return store that fit at least one of the categoriesthe problem is right now the search seems to be returning everything

146

$java char '\249' “not a statement” and “unclosed character literal”$

Java