Joining rows from two tables

Question

I have two tables Table1 and Table2. I wanted to join the two tables data based on the TIME_STAMP data field

I have tried the below query but I am not able to get the expected result

Table 1

CATEGORY_ID ID TIME_STAMP VALUE ------------------------------------- 1 444 30-Mar-17 XXX 1 444 31-Jul-18 YYY 1 444 15-Jan-19 ZZZ

Table 2

CATEGORY_ID ID TIME_STAMP VALUE ------------------------------------------ 2 444 30-Mar-17 10/31/2017 2 444 30-May-18 10/25/2018 2 444 13-Jun-19 10/25/2018

Actual Result:

TIME_STAMP Table 1 VALUE Table 2 value ------------------------------------------- 30-Mar-17 XXX 10/31/2017 31-Jul-18 YYY NULL 15-Jan-19 ZZZ NULL

Query :

SELECT T1.TIME_STAMP , T1.X_VALUE, T2.X_VALUE FROM TABLE1 T1 LEFT OUTER JOIN TABLE2 T2 ON T1.ID = T2.ID AND TO_CHAR(T1.TIME_STAMP,'MM/DD/YYYY') =TO_CHAR(T2.TIME_STAMP,'MM/DD/YYYY') AND T2.CATEGORY_ID=2 WHERE T1.CATEGORY_ID =1 AND T1.ID= 444

Expected Result:

TIME_STAMP Table1 VALUE Table2 VALUE ----------------------------------------- 30-Mar-17 XXX 10/31/2017 30-May-18 NULL 10/25/2018 31-Jul-18 YYY NULL 15-Jan-19 ZZZ NULL 13-Jun-19 NULL 10/25/2018

Which variant of SQL are you using? (MySQL, MSSQL, etc.)

Kei
– Kei

2019-09-12 07:49:50 +00:00
Commented Sep 12, 2019 at 7:49 — Kei
– Kei, Commented Sep 12, 2019 at 7:49
@Kei : I am using Oracle

Nizam Muhammed
– Nizam Muhammed

2019-09-12 08:58:37 +00:00
Commented Sep 12, 2019 at 8:58 — Nizam Muhammed
– Nizam Muhammed, Commented Sep 12, 2019 at 8:58

Gordon Linoff · Accepted Answer · 2019-09-12 11:44:32Z

1

FULL OUTER JOIN with filtering is tricky. I recommend using a subquery for the filtering criteria:

select coalesce(t1.time_stamp, t2.time_stamp) as time_stamp, t1.x_value, t2.x_value from (select t1.* from table1 t1 where t1.CATEGORY_ID = 1 and T1.ID = 444 ) t1 full join (select t2.* from table2 t2 where t2.id = 444 and t2.category_id = 2 ) t2 on t2.id = t1.id and trunc(t2.time_stamp) = trunc(t1.time_stamp);

answered Sep 12, 2019 at 11:44

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nizam Muhammed Over a year ago

Thanks a lot @Gordon Linoff , It's perfectly working . Can you please suggest me ,if I have another table(Total 3 tables like Table1,Table2 and Table3) with the same design with different CATEGORY_ID data. How can I add that condition to the subquery.

Gordon Linoff Over a year ago

@NizamMuhammed . . . You would just add another subquery and another JOIN. If you don't see how to do that, you can ask a new question, with appropriate sample data, desired results, and so on.

craigpastro · Accepted Answer · 2019-09-12 07:54:11Z

Based on your expected result I think you want to do a FULL OUTER JOIN on the TIME_STAMP column.

DxTx · Accepted Answer · 2019-09-12 08:35:09Z

You could do something like this.

SELECT COALESCE(t1.time_stamp, t2.time_stamp) AS TIME_STAMP, t1.value as T1_value, t2.value as T2_value FROM table01 t1 FULL OUTER JOIN table02 t2 ON t1.time_stamp = t2.time_stamp

+-------------+-----------+------------+ | TIME_STAMP | T1_value | T2_value | +-------------+-----------+------------+ | 2017-03-30 | XXX | 10/31/2017 | | 2018-07-31 | YYY | (null) | | 2019-01-15 | ZZZ | (null) | | 2018-05-30 | (null) | 10/25/2018 | | 2019-06-13 | (null) | 10/25/2018 | +-------------+-----------+------------+

^{Note: I have used SQL Server since you haven't mentioned a DBMS.}

Thanks @DxTx : for your time and query. I tried the above query but I am getting other data which is of other time stamp. Do I no need to mentioned the CATEGORY_ID and ID?.As there is other categories data also in the tables.

woo2333 · Accepted Answer · 2019-09-12 09:38:14Z

before you join the two tables with timestamp, you need trunc the timestamp to Date. like TRUNC("TimeStamp", DATE)

SELECT COALESCE(t1.time_stamp, t2.time_stamp) AS TIME_STAMP, t1.value as T1_value, t2.value as T2_value FROM table01 t1 FULL OUTER JOIN table02 t2 ON trunc(t1.time_stamp, 'DATE') = trunc(t2.time_stamp, 'DATE');

I have used "TO_CHAR(T1.TIME_STAMP,'MM/DD/YYYY') " as it is a oracle but still it display other data . As i also need to put the condition for CATEGORY_ID and ID. Please suggest and Thank you .

Collectives™ on Stack Overflow

Joining rows from two tables

4 Answers 4

2 Comments

Comments

1 Comment

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

1 Comment

1 Comment

Related