Forum Stats

  • 3,733,254 Users
  • 2,246,738 Discussions
  • 7,856,636 Comments

Discussions

SQL Join help required!

624327
624327 Member Posts: 2
edited February 2008 in SQL & PL/SQL
Hi all

I need help with a join.

If you click the image link below, I need to find a way to join table 1 and table 2 to get table3:
http://img229.imageshack.us/img229/1401/83192078uq8.jpg


Help would be very much appreciated!
Thanks in advance.

Comments

  • 297403
    297403 Member Posts: 349
    what you need is an union of the 2 tables, try something like this:

    select projectid, firstname, lastname,..., null as month, null as year
    from table1
    union
    select projectid, null as firstname, null as lastname,...., month, year
    from table2
  • 624327
    624327 Member Posts: 2
    If I use UNION I will have to ensure that both tables have the same number of colums.

    What is the best way to 'create' these columns filled with null values that can be used with the UNION?
  • 623666
    623666 Member Posts: 174
    Oh great question.
    select a.*,b.*
    from table1 a full join table2 b
    on 1=0;
    Above solution like "Outer union".
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    with Table1 as(select 1234 as ProjectID,'Smith' as LastName from dual
    union select 1234,'Maria' from dual
    union select 1234,'Victo' from dual),
    Table2 as (select 1234 as ProjectID,200 as Val from dual
    union select 1234,300 from dual
    union select 1234,400 from dual)
    select nvl(a.ProjectID,b.ProjectID) as ProjectID,a.LastName,b.Val
    from Table1 a full join Table2 b
    on 1=0;
    PROJECTID  LASTN  VAL
    --------- ----- ----
    1234 Maria null
    1234 Smith null
    1234 Victo null
    1234 null 200
    1234 null 300
    1234 null 400
This discussion has been closed.