Re: How to join tables

Originally posted by Jn
> I have these two tables
>
> Calendar (rows are pregenerated for every day)
>
> DATE DAY
> ============
> 1.5.2003 Th
> 2.5.2003 Fr
> 3.5.2003 Sa
> ...
>
> And Data
>
> DATE TIME
> =============
> 2.5.2003 10:30
> 2.5.2003 14:00
> 4.5.2003 12:00
> 4.5.2003 17:00
> 4.5.2003 19:00
> ...
>
> I am not able to join these two tables so the result would be this
>
> DATE TIME
> ==============
> 1.5.2003 NULL
> 2.5.2003 10:30
> 2.5.2003 14:00
> 3.5.2003 NULL
> 4.5.2003 12:00
> 4.5.2003 17:00
> 4.5.2003 19:00
>
> Using outer join always selects only rows which exist in Data. One
> condition - I cannot use UNION ! Please help, thanks.
Something like this:

SQL> create table t1 (d date);

Table created.

SQL> insert into t1
  2 select trunc(sysdate)+rownum from dept;

4 rows created.

SQL> select * from t1;

D

02-JUL-2003
03-JUL-2003
04-JUL-2003
05-JUL-2003

SQL> create table t2(dt date);

Table created.

SQL> insert into t2
  2 select sysdate+rownum from dept;

4 rows created.

SQL> delete t2
  2 where trunc(dt) = trunc(sysdate)+1;

1 row deleted.

SQL> select to_char(dt,'DD-MON-YYYY HH24:MI:SS') dt from t2;

DT

03-JUL-2003 10:51:42
04-JUL-2003 10:51:42
05-JUL-2003 10:51:42

SQL> select t1.d, to_char( t2.dt, 'HH24:MI:SS')   2 from t1, t2
  3* where t1.d = trunc(t2.dt (+));

D TO_CHAR(

----------- --------
02-JUL-2003
03-JUL-2003 10:51:42
04-JUL-2003 10:51:42
05-JUL-2003 10:51:42

--
Posted via http://dbforums.com