Results 1 to 3 of 3

Thread: LEFT JOIN and IN SUBQUERIES giving DIFFERENT results

  1. #1
    Join Date
    Oct 2011
    Posts
    1

    LEFT JOIN and IN SUBQUERIES giving DIFFERENT results

    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
    )

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Which result set is correct?

  3. #3
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    The LEFT JOIN may be retrieving more rows because of multiple transactions by the same customer. You are getting product of number of rows for a customer in the table. That means a customer has 3 records then you get 9 rows returned.

    In the second case, you get only one record back.

    In both cases, if the customer paid for one item before and has not paid for others, then your logic still allows the customer to make a purchase. You need to look for all records with payment or use NOT IN with no payment.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •