Join Two Tables and get the output from both of them

Question

Possible Duplicate:
SQL Query JOIN with Table

If this is the data in TestingTable1

BUYER_ID  |   ITEM_ID       |  CREATED_TIME
----------+-----------------+----------------------
1345653      151851771618     2012-07-09 19:57:33
1345653      110909316904     2012-07-09 21:29:06
1345653      221065796761     2012-07-09 19:31:48

And if this is the below data in TestingTable2

USER_ID  |   PRODUCT_ID    |    LAST_TIME
---------+----------------+-----------------------
1345653     150851771618      2012-07-09 19:57:33
1345653     110909316904      2012-07-09 22:29:06
1345653     221165796761      2012-07-09 12:31:48

I need to compare TestingTable2 with TestingTable1 on BUYER_ID and USER_ID. I need to see, if BUYER_ID and USER_ID gets matched then I need to compare ITEM_ID with PRODUCT_ID and CREATED_TIME with LAST_TIME and if there is a mismatch in TestingTable2 after comparing with TestingTable1 in either one of them or both of them, then I need to show the result.

So if you look at the above example- I have three scenarios basically

1. Firstly- In TestingTable1, in the First row ITEM_ID is not matching with PRODUCT_ID in the First row of TestingTable2 but CREATED_TIME is matching with LAST_TIME for the first row in both the tables
2. Secondly- In TestingTable1, in the Second row CREATED_TIME is not matching with LAST_TIME in the second row of TestingTable2 but ITEM_ID is matching with PRODUCT_ID for the second row in both the tables
3. Thirdly- In TestingTable1, in the Third row ITEM_ID is not matching with PRODUCT_ID and also CREATED_TIME is not matching with LAST_TIME, so in the third row BOTH of them does not match with TestingTable1 third row.

So these are three case that I need to cover while comparing TestingTable2 with TestingTable1 always. And TestingTable1 is the MAIN table through which comparisons need to be made always, so it means data in TestingTable1 is always accurate.

So I need to show the result like this considering the above example if not matching either one of them or both of them- TestingTable1 data then next to it same TestingTable2 data, so that I can see what value was there in TestingTable1 as compared to TestingTable2

BUYER_ID   |   ITEM_ID       |    CREATED_TIME           |      USER_ID   |     PRODUCT_ID     |     LAST_TIME   
-----------+-----------------+---------------------------+----------------+--------------------+-----------------------
1345653        151851771618       2012-07-09 19:57:33           1345653        150851771618         2012-07-09 19:57:33
1345653        110909316904       2012-07-09 21:29:06           1345653        110909316904         2012-07-09 22:29:06
1345653        221065796761       2012-07-09 19:31:48           1345653        221165796761         2012-07-09 12:31:48

So I wrote a query, I thought it will cover all my three scenarios, but Only it covered First Two not the Third One. And I am confuse whether we can achieve this third scenario or not?

SELECT * 
FROM(
    SELECT *
    FROM TestingTable1 A
    JOIN TestingTable2 B ON A.BUYER_ID = B.USER_ID AND B.LAST_TIME = A.Created_TIME 
    WHERE B.PRODUCTID <> A.ITEM_ID
    UNION ALL
    SELECT * 
    FROM TestingTable1 A
    INNER JOIN TestingTable2 B ON A.BUYER_ID = B.USER_ID AND B.PRODUCTID = A.ITEM_ID  
    WHERE B.t1time <> A.Created_TIME  
 ) X    

Any suggestions will be appreciated.

Update:-

Just a quick update what I was initially thinking to do. As I was aware of few problems with my third scenario.

First of all in TestingTable1, I am sorting(ORDER BY) the table by BUYER_ID and CREATED_TIME and same with TestingTable2 I am sorting with USER_ID and LAST_TIME and I am doing comparison by making sure data belongs to BUYER_ID and USER_ID on a given day.

Answer 1

You could use a non exists clause to find rows in one table that are not matched in the other. With union you could repeat this for the other table:

select  'missing in t2', *
from    TestingTable1 t1
where   not exists
        (
        select  *
        from    TestingTable2 t2
        where   t1.buyer_id = t2.user_id
                and t1.item_id = t2.product_id
                and t1.created_time = t2.last_time
        )
union all
select  'missing in t1', *
from    TestingTable2 t2
where   not exists
        (
        select  *
        from    TestingTable1 t1
        where   t1.buyer_id = t2.user_id
                and t1.item_id = t2.product_id
                and t1.created_time = t2.last_time
        )

Live example at SQL Fiddle.

Answer 2

with C as
(
  select *
  from TestingTable1 A
    inner join TestingTable2 B
      on A.BUYER_ID = B.USER_ID and
         B.LAST_TIME = A.Created_TIME and
         B.PRODUCT_ID <> A.ITEM_ID
  union all
  select * 
  from TestingTable1 A
    inner join TestingTable2 B 
      on A.BUYER_ID = B.USER_ID and 
         B.PRODUCT_ID = A.ITEM_ID and
         B.LAST_TIME <> A.CREATED_TIME
)
select *
from C
union all
select *
from TestingTable1 A
  inner join TestingTable2 B
    on A.BUYER_ID = B.USER_ID and
       A.CREATED_TIME <> B.LAST_TIME and
       A.ITEM_ID <> B.PRODUCT_ID
where not exists (select *
                  from C
                  where A.BUYER_ID = C.BUYER_ID and
                        A.ITEM_ID = C.ITEM_ID and
                        A.CREATED_TIME = C.CREATED_TIME) and
      not exists (select *
                  from C
                  where B.USER_ID = C.USER_ID and
                        B.PRODUCT_ID = C.PRODUCT_ID and
                        B.LAST_TIME = C.LAST_TIME);

SQL Fiddle

Answer 3

The closest you can get is matching all rows in TestingTable1 without a match in TestingTable2 with every possible not-matched row in TestingTable2.

Like this (untested, but hopefully you get the idea):

SELECT *
FROM TestingTable1 AS T1
INNER JOIN TestingTable2 AS T2 ON T1.BUYER_ID = T2.USER_ID
    AND
    (
        (
            (T1.ITEM_ID = T2.PRODUCT_ID AND T1.CREATED_TIME <> T2.LAST_TIME)
            OR (T1.ITEM_ID <> T2.PRODUCT_ID AND T1.CREATED_TIME = T2.LAST_TIME)
            OR 
            (
                T1.ITEM_ID <> T2.PRODUCT_ID AND T1.CREATED_TIME <> T2.LAST_TIME
                AND NOT EXISTS(SELECT 1
                       FROM TestingTable2 AS T2a
                       INNER JOIN TestingTable1 AS T1a ON T2a.USER_ID = T1a.BUYER_ID
                       AND 
                       (
                           (T1a.ITEM_ID = T2a.PRODUCT_ID AND T1a.CREATED_TIME <> T2a.LAST_TIME)
                           OR (T1a.ITEM_ID <> T2a.PRODUCT_ID AND T1a.CREATED_TIME = T2a.LAST_TIME)
                       )
                       WHERE T1a.BUYER_ID = T1.BUYER_ID
                       AND (T2a.PRODUCT_ID <> T2.PRODUCT_ID OR T2a.LAST_TIME <> T2.LAST_TIME)                       
                      )
            )
        )
    )

That is, if you had an additional row in TestingTable2:

USER_ID  |   PRODUCT_ID   |    LAST_TIME
---------+----------------+-----------------------
1345653     333333333333      2012-07-09 05:27:18

your result set would look like:

BUYER_ID   |   ITEM_ID       |    CREATED_TIME           |      USER_ID   |     PRODUCT_ID     |     LAST_TIME   
-----------+-----------------+---------------------------+----------------+--------------------+-----------------------
1345653        151851771618       2012-07-09 19:57:33           1345653        150851771618         2012-07-09 19:57:33
1345653        110909316904       2012-07-09 21:29:06           1345653        110909316904         2012-07-09 22:29:06
1345653        221065796761       2012-07-09 19:31:48           1345653        221165796761         2012-07-09 12:31:48
1345653        221065796761       2012-07-09 19:31:48           1345653        333333333333         2012-07-09 05:27:18

Note that this is all assuming you have one and only one related row in TestingTable2 for each mistaken PRODUCT_ID or LAST_TIME. Otherwise you will get all sorts of crazy Cartesian-product matches! In order to avoid this, you need to determine an ordering for one or both of the tables to determine which row should get matched first.

For example, try adding the following row, which satisfies both scenario 1 and 2, to TestingTable2 and see what happens:

USER_ID  |   PRODUCT_ID   |    LAST_TIME
---------+----------------+-----------------------
1345653     110909316904      2012-07-09 19:57:33

Answer 4

There is one major problem with the entire attempt.

Because the UserID/BuyerID are identical in every row, the query compares every row from TestingTable1 with every row from TestingTable2.

It is only by coincidence that the first two comparisons worked for you;
If you had several rows with similar CREATED_TIME (even with different ITEM_IDs) they would be compared to each other.

What I recommend is this:
Add a primary key for each table, and a foreign key that will connect them.
That way, you will be able to compare each row in TestingTable1 to its equivalent in TestingTable2, without result in a "Cartesian product".