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
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'
";
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
This seems a touch nonsensical to me:
AND i.ipactualpaymentdt >= '2019-02-04'
AND i.ipactualpaymentdt <= '2019-02-04'
Firebase Cloud Functions: PubSub, "res.on is not a function"
TypeError: Cannot read properties of undefined (reading 'createMessageComponentCollector')
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
This question already has an answer here: