Home » SQL & PL/SQL » SQL & PL/SQL » Join diffrent tables
Join diffrent tables [message #293269] Fri, 11 January 2008 08:59 Go to next message
Hunter_wow
Messages: 31
Registered: November 2007
Member
Hello. I´m having problem to join tables with diffrent number of columns. And I would not like to lose any data.
The closest way to do what I want was UNION ALL but dose not work with diffrent number of columns and JOIN would like the tables to have something in common right?

I add an picture to show how I would like the result to become:

/forum/fa/3731/0/

Greatful for all tips.
  • Attachment: pic.gif
    (Size: 5.78KB, Downloaded 829 times)
Re: Join diffrent tables [message #293278 is a reply to message #293269] Fri, 11 January 2008 09:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Use UNION ALL just add NULL in SELECT for the rows that contain less columns.

Next time, instead of an image, please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Also post your Oracle version (4 decimals) and a test case: create table and insert statements.

Regards
Michel

[Updated on: Fri, 11 January 2008 09:29]

Report message to a moderator

Re: Join diffrent tables [message #293304 is a reply to message #293269] Fri, 11 January 2008 11:25 Go to previous messageGo to next message
Hunter_wow
Messages: 31
Registered: November 2007
Member
ok, thanks a lot. Will try it =)
Re: Join diffrent tables [message #293522 is a reply to message #293269] Sun, 13 January 2008 22:07 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
SQL> select * from test1;

         A          B
---------- ----------
         1          2
         3          4
         5          6

SQL> select * from test2;

         A          B          C
---------- ---------- ----------
        11         22         33
        44         55         66
        77         88         99

SQL> select a,b,decode(c,0,'null',c) as c
  2  from (select a,b,0 as c from test1
  3  union
  4  select a,b,c from test2);

         A          B C
---------- ---------- --------------------------------------
         1          2 null
         3          4 null
         5          6 null
        11         22 33
        44         55 66
        77         88 99

6 rows selected.
Re: Join diffrent tables [message #293532 is a reply to message #293522] Sun, 13 January 2008 23:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Why not directly put a NULL (a real NULL not the string 'null') in the subquery?

Regards
Michel
Re: Join diffrent tables [message #293567 is a reply to message #293269] Mon, 14 January 2008 02:14 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
not too much difference.
SQL> ed
Wrote file afiedt.buf

  1  select a,b,decode(c,null,'null',c) c
  2  from (select a,b,null as c from test1
  3  union
  4* select a,b,c from test2)
SQL> /

         A          B C
---------- ---------- ----------------------------------------
         1          2 null
         3          4 null
         5          6 null
        11         22 33
        44         55 66
        77         88 99

6 rows selected.
Re: Join diffrent tables [message #293570 is a reply to message #293567] Mon, 14 January 2008 02:29 Go to previous message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You miss my point.
The result should be NULL and NOT the string "null".
Even if it was the string "null" there is no use of an outer query.

Regards
Michel
Previous Topic: To find the available time intervals for a day
Next Topic: Formulization at Query level
Goto Forum:
  


Current Time: Thu Feb 06 23:17:55 CST 2025