Hi guys,

I need your help. I have been having this problem for 2 months and still cannot understand why these 2 queries are giving me different number of rows. I tried retrieving results in 2 ways: left joins and IN subqueries...

My goal is to get all customers that have paid before their current transaction of ordering new items - so basically when TRANSACTION_TYP=PAYMENT and transaction_date is less than current transaction date. The subquery is giving me less rows than the left joins. can anybody tell me why?? pls help

Here are my queries:
LEFT JOIN:
SELECT
A.CUST_ID,
A.TRANSACTION_DATE,
A.ORDER_AMT , A.TRANSACTION_TYP, B.TRANSACTION_TYP
FROM CUSTOMER_TABLE A LEFT JOIN CUSTOMER_TABLE B ON A.CUST_ID=B.CUST_ID AND B.TRANSACTION_TYP=’PAYMENT’ AND B.TRANSACTION_DATE<A.TRANSACTION_DATE
WHERE B.TRANSACTION_TYP IS NOT NULL

SUBQUERY:
SELECT CUST_ID, TRANSACTION_DATE, ORDER_AMT, TRANSACTION_TYP
FROM CUSTOMER_TABLE LEFT WHERE CUST_ID IN
(
SELECT B.CUST_ID FROM CUSTOMER_TABLE B WHERE B.CUST_ID=A.CUST_ID AND B.TRANSACTION_TYP=’PAYMENT’ AND B.TRANSACTION_DATE<A.TRANSACTION_DATE
)