Three tables to one

[EMW]

Hi,

I have an Oracle database with different tables of data.
They all have one common field.
I need to combine some fields from three tables.
i.e.
field1 and field2 from table1
field3 and field4 from table2
field5 from table3

Alltogether I want to produce a dataset with one table with the following
schema: common, field1, field2, field3, field4, field5

How can I do this?
Can I do it by excecuting a SQL command to the database or do I combine the
data within the dataset or is there some other way of doing this?

My program is in VB.NET.

rg,
Eric

 

[Marina]

The most efficient way, would be to do joins in your SQL query, so this is
all done at the database level.

 

[Peter Rilling]

To expand, you can use a view to format the data appropriately and then all
the client has to do is select information from the view.

 

[EMW]

could you give me an example?
I've searched for some examples on joining tables, but couldn't get it to
work.

rg,
Eric

 

[UAError]

could you give me an example?
I've searched for some examples on joining tables, but couldn't get it to
work.

rg,
Eric

Probably because you should have been looking for UNION
instead of JOIN, e.g.:

(
SELECT commonA, field1, field2,
'constA3', 'constA4', 'constA5'
FROM tableA WHERE ...
)UNION(
SELECT commonB, 'constB1', 'constB2',
field3, field4, 'constB5'
FROM tableB WHERE ...
)UNION(
SELECT commonC, 'constC1', 'constC2',
'constC3', 'constC4', field5
FROM tableC WHERE ...
);


you could use UNION ALL to preserve duplicates between
SELECTs - but I don't think that applies here.
Note that the column value's data types (or the "constant"
replacements) in each of the SELECT's column positions need
to be compatible.
'Any fool can write code that a computer can understand.
Good programmers write code that humans can understand.'
Martin Fowler,
'Refactoring: improving the design of existing code', p.15

 

[Cor Ligthert]

I hate SQL so I always try too avoid it.

However being active in these newsgroups is every time learning more of it.

Eric, when this works, will you than answer that here, this can be a great
addition.

Cor