Hi guys, I need your help. I have been having this problem for 2 months and stil

Question

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 A.CUST_ID, A.TRANSACTION_DATE, A.ORDER_AMT, A.TRANSACTION_TYP
FROM CUSTOMER_TABLE A WHERE A.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
)

Explanation / Answer

Hi, since I don't have any of your data at hand to actually test with it, I'll just play this by ear and go with what has always made sense to me in DB querying :)

If this still doesn't fix your problem, then you can PM me and we can go through your data by inbox. Otherwise, if this somehow does help you solve your issue -,then please remember to rate :)))

One thing before we look into each one in detail - when you say transaction date less than current transaction date - does that mean you HAVE a specific concrete value (dd/mm/yyyy) at hand for the transaction? or is the current transaction just the most recent transaction - which is to say the MAX() transaction on record so far ? If your query is just general like that, then it will even return results of payment transactions from 1999 if there happens to be a date of a transaction after that in 2000, it fits, but it's NOT the current date.

So for simplicity I'll go with the assumption that you have an actual date value DATE that's a dd/mm/yyyy either provided by the querier OR created using the SQL now() function to return the current system date OR using a sub-query to get the latest date on record.

Summary - depensing on what your conditions are, when you see Date in a query, substitute whichever one of these that applies:

Date = '01/01/2011' OR

Date = now() OR

Date = (SELECT MAX(TRANSACTION_DATE) FROM CUSTOMER_TABLE WHERE CUST_ID = outer.CUST_ID)

Here is how I would nomally go about writing that query:

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
WHERE
B.TRANSACTION_TYP=’PAYMENT’ AND B.TRANSACTION_DATE< Date

As for the subquery, I'm not sure if you're familiar with the difference between a correlated and regular subquery - if so, then you know and purposefully correlated the outer and innser queries, making the query behave as an outer-loop-and-nested-loop search. That usually isn't necessary when all you want is to extract a subset of rows (the ones from the inner query fulfilling your conditions) from another bigger Set (the ones from the outer query which are the result of a subtraction of (rows with cus_id matching the inner query) from the entire table). What I'm trying to say here is that linking the inner customer_id with the outer customer_id in the subquery makes things more complicated than they need to be, I can't tell specifically what results you'll get with that, but with a simpler subquery like the following - the expected behaviour is more obvious and likely to be achieved:

SELECT
A.CUST_ID, A.TRANSACTION_DATE, A.ORDER_AMT, A.TRANSACTION_TYP
FROM CUSTOMER_TABLE A
WHERE A.CUST_ID IN
(
   SELECT CUST_ID FROM CUSTOMER_TABLE
   WHERE TRANSACTION_TYP=’PAYMENT’ AND TRANSACTION_DATE < Date
)

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.