How to JOIN query to a Table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to JOIN query to a Table

Author

Topic

raihan26
Starting Member

8 Posts

Posted - 2012-07-08 : 21:48:17

This is the below Table1 data which I am getting back from this query


select user_id, prod_and_ts.product_id as product_id, prod_and_ts.timestamps as timestamps from testingtable2 LATERAL VIEW explode(purchased_item) exploded_table as prod_and_ts

Output from the above query.


    USER_ID     |    PRODUCT_ID    |   TIMESTAMPS
    ------------+------------------+-------------
    1015826235       220003038067     *1004941621*
    1015826235       300003861266      1005268799
    1015826235       140002997245      1061569397
    1015826235      *210002448035*     1005542471

If you compare the `Table1` data with the below `Table2` data, then the `PRODUCT_ID` in the last line of Table1 data is not matching with the `ITEM_ID` in the last line in the below Table2 data and also same with `TIMESTAMPS` in the first line of `Table1` data is not matching with `CREATED_TIME` in the first line of `Table2` data.


    BUYER_ID     |    ITEM_ID         |      CREATED_TIME 
    -------------+--------------------+------------------------
    1015826235	     220003038067	    *2001-11-03 19:40:21*
    1015826235	     300003861266	     2001-11-08 18:19:59
    1015826235	     140002997245	     2003-08-22 09:23:17
    1015826235      *200002448035*           2001-11-11 22:21:11

So I need to show the result like this for the above example after JOINING Table1 with Table2-


    BUYER_ID    |     ITEM_ID       |    CREATED_TIME         |    PRODUCT_ID     |    TIMESTAMPS
    ------------+-------------------+-------------------------+-------------------+-----------------
    1015826235	     220003038067	*2001-11-03 19:40:21*      220003038067       *1004941621*
    1015826235      *200002448035*       2001-11-11 22:21:11      *210002448035*       1005542471

Can anyone suggest me how to JOIN the Query that I wrote with Table2 data to get the actual result that I am looking for.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-08 : 22:57:29

you need to explain us what basis you think the tables can be joined so as to get the last row in result as its obviously not straight match.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

raihan26
Starting Member

8 Posts

Posted - 2012-07-08 : 23:02:43

If BUYER_ID and USER_ID gets matched then we will compare ITEM_ID with PRODUCT_ID and TIMESTAMPS with CREATED_TIME corresponding to that particular BUYER_ID and USER_ID. If they are not matched then I need to show them, If you see my above output, in which ITEM_ID was not matched with PRODUCT_ID and TIMESTAMPS was not matched with CREATED_TIME.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-09 : 00:08:19

you can get unmatched items from both table. but question is on what basis you determine which row from table 1 should come against which row in table2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

raihan26
Starting Member

8 Posts

Posted - 2012-07-09 : 00:14:45

Ok. Just see Table1 data and Table2 data and PRODUCT_ID is same thing as ITEM_ID and TIMESTAMPS are same thing are CREATED_TIME. So basically both of the table store same thing. So I need to make a comparison between Table1 and Table2 and Table2 is the main table through which comparisons need to be made. So my point here is that. Anything in Table2 corresponding to BUYER_ID is accurate, so I need to compare ITEM_ID with PRODUCT_ID and TIMESTAMPS with CREATED_TIME corresponding to each BUYER_ID(USER_ID) from Table2 and if they didn't matched then that means there is data discrepancy, so that is the reason I thought to make an output like that, so that people can understand, Table2 has this data and Table1 has wrong data side by side.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources