Link to home
Get AccessLog in
Avatar of bobdylan75
bobdylan75Flag for Afghanistan

asked on

db2 sql : join in both directions

Hi,
need to have a TYPE OF JOIN that connects to both records in the table on the left that those in the right table,
even if they do not match each other..
in example:
in the left table I have 3 records,
1
3
5

in right table I have 3 records,
1
4
6

the result join table has to have 5 elements:
1
3
4
5
6

(I dont need a UNION sql because my real table has more fields different in the two tables)
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

Hi Bob,

If you simply want all of the keys in the two files:

  SELECT key FROM table1
  UNION
  SELECT key FROM table2;

Then join the data to that as a subquery.


SELECT *
FROM
(
  SELECT key FROM table1
  UNION
  SELECT key FROM table2
) Keys
LEFT JOIN table1
  ON KEYS.key = table1.key
LEFT JOIN table2
  ON KEYS.key = table2.key

That's not very efficient, but it should work for you.


Kent




Avatar of bobdylan75

ASKER

Kdo,
you say there isn't a special JOIN to do this?
ASKER CERTIFIED SOLUTION
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
ok
thanks!
Hi Bob,

I need to brush up on my AS/400 versions.  They've made huge improvements the last few releases...


Kent