Problem with joining db tables

Question

I have problem when joining tables (left join)

table1:

id1  amt1
1    100
2    200
3    300

table2:

id2  amt2
1    150
2    250
2    350

my Query:

select id1,amt1,id2,amt2 from table1
left join table2 on table2.id1=table1.id2

My supposed o/p is:

      id1 amt1  id2 amt2
row1: 1   100   1    150
row2: 2   200   2    250
row3: 2   200   2    350

I want o/p in row3 as

2 null 2 350

ie I want avoid repetition of data(amt1).

Answer 1

Assuning you want all data in a single row, you just do a union select...

Select fieldA from tableA Union Select fieldB from TableB

Note that you need to cast the datatype to be the same for both tables.

If you need an other answer, please format the expected result better ;)

Ok...

You have corrected the formating...

In the case above I would simply return 2 cursors from my query. The example data provides no field to link both tables together, so i see no way to join them in a resonable manner. It is possible for a sproc to return several resultsets.

Answer 2

You've done the cartesian product of the two tables since you haven't specified any join criteria. In order to eliminate duplicates, you need to specify how you want the tables to join.

For example, you could try

select * from table1, table2 where table2.val = 111;

Your example doesn't have any join key, so there's no obvious value to join the tables on. But is a more typical example, there would be a related value in both tables so that you could join them together in a meaningful way.

Answer 3

You seem to be doing a cross join here. I suspect you wanted either an equi join or a left outer join.

Answer 4

This really is a formatting issue which is best handled by the client. For instance, in SQL*Plus we can use BREAK ....

SQL> select t1.*, t2.* from t1, t2
  2  /

A   B   C   D           C1
--- --- --- --- ----------
aaa bbb ccc ddd        111
aaa bbb ccc ddd        222

SQL> break on a on b on c on d
SQL> select t1.*, t2.* from t1, t2
  2  /

A   B   C   D           C1
--- --- --- --- ----------
aaa bbb ccc ddd        111
                       222

SQL>

Note: in the absence of any further information I opted for a Cartesian product.

edit

BREAK is a SQL*Plus command, which suppresses duplicate columns in our rows. It only works in the SQL*Plus client. As might be expected, it is covered in Oracle's SQL*Plus User Guide. Find out more.

I used BREAK as an example of the proper way of doing things, because it is clean and correctly implements the separation of concerns. It you are using a different client you would need to use its formatting capabilities. It is possible to tweak the SQL (see below) but that diminishes the utility of the query, because we cannot reuse the query in other places which don't want to suppress the duplicated values.

Anyway, here is one solution which uses the ROW_NUMBER() analytic function in an inline view.

SQL> select * from t1
  2  /

A   B   C   D           ID
--- --- --- --- ----------
eee fff ggg hhh          1
aaa bbb ccc ddd          2

SQL> select * from t2
  2  /

        C1         ID
---------- ----------
       333          2
       111          1
       222          2
       444          2

SQL> select t1_id
  2         , case when rn = 1 then a else null end as a
  3         , t2_id
  4         , c1
  5  from (
  6      select t1.id as t1_id
  7             , row_number () over (partition by t1.id order by t2.c1) as rn
  8             , t1.a
  9             , t2.c1
 10             , t2.id as t2_id
 11      from t1, t2
 12      where t1.id = t2.id
 13      )
 14  order by t1_id, rn
 15  /

     T1_ID A        T2_ID         C1
---------- --- ---------- ----------
         1 eee          1        111
         2 aaa          2        222
         2              2        333
         2              2        444

SQL>

I chose not to use LAG(), because that only works with fixed offsets, and it seemed likely that the number of rows in T2 would be variable.