data from multiple tables

[tyb]

Dear Frends

i've three data tables where one is the parent while two are the child tables.

ie table1 (it_no,Description)
table2 (it_no,Despatched,to,date)
table3 (it_no,Received,From,date)

now i want to have the data regarding IT_NO eg

IT No. 1 Desc: GP Boxes

Date Despatched Received To/From
10-05 1000 Siemenc Corp.
10-05 500 AK Group
11-05 200 Azal Co.


but when i do it i get a record from table2 against each record of the table3. and my results show this way

IT No. 1 Desc: GP Boxes

Date Despatched Received To/From
10-05 1000 Siemenc Corp.
10-05 500 AK Group
11-05 200 Azal Co.
11-05 200 Azal Co.


any solution plz

 

[nagornyi]

Looks like you just linked tables 2 and 3 by it_no, so the records get multiplied according to the Cartesian product rule. The simplest solution would be if actual duplicate records not possible in the tables. In this case just add DISTINCT to your SELECT when joining tables 2 and 3. If duplicate records possible, you may use UNION query:

select
      it_no
     , date
     , Dispatched
     , null Received
     , to to_from
from table2
union
select
      it_no
     , date
     , null Dispatched
     , Received
     , from to_from
from table3

Note. The above SQL shows field names as in the original question. In reality it is not possible to have fields named to, from, date bacause those are reserved words.

 

[tyb]

hi dear

i tried it without and with dual table but get the error
ORA-01790: expression must have same datatype as corresponding expression
though all the fields are of char data type
the query is as follows

select ca_l4.it_code4 item,ca_l4.it_desc it_desc,null issue_no,null rcpt_no
from ca_l4,dual
union
select receipt.item_code item,null it_desc,null issue_no,receipt.rcpt_no rcpt_no
from receipt,dual
union
select issue.item_code item,null it_desc,issue.issue_no issue_no,null rcpt_no
from issue,dual

plz help

 

[nagornyi]

I thing the problem is with NULLs. Try replacing then with "space" characters.