Hi,

I have two tables like these: (this is an example, the actual tables
have diffferent fields and meanings)

TABLE1
id
person_name
date_arrival
date_departure

TABLE2
id
car_description
date_arrival
date_departure

I'd like to make a query to have such resulting table

RESULTING_TABLE
id
person_name
car_description
date_arrival
date_departure

the id is the primary key for the three tables and it's unique for the
three (id in table1 cannot be in table2, I use the same counter to
generate the id for table1 and table2).


I is possible to create such a query?

Thanks

filippo wrote:
Yes. But I wonder why on earth you want to join two distinct sets of
data into a single result. I smell homework assignment...

Kind regards

robert

Robert Klemme ha scritto:

no, this is my app (10k lines of perl/Tk code). It is a campsite
manager program: I have two tables

a) presents
the information of people registered (id, arrival date, arrival print
module, etc)
b) moviments
the information about anithing else but people (dogs, cars registered,
etc)

to calculate the whole bill I need to search into both tables to
collect all the informations about a group. Instead of repeating the
same procedures for both tables I want to query the two tables into a
single table merging data into same column (arrival_data is present on
both the table so I want to have one single column in the new table,
other fields are not present in both tables so shoud create different
columns).

That's all

filippo wrote:
Yes.


Oh you want help writing it. okay, to help us help you, Can you provide
data to show what you really want?

example: If user id 5 has car id 27, what value is displayed?

ed

Ed Prochak ha scritto:
TABLE: person_tbl
id
name
arrival_date
age

TABLE: tools_tbl
id
description
arrival_date

I want
TABLE: result_tbl
id
name
description
arrival_date

Is my solution this?

SELECT id, name, arrival_date, age FROM prerson_tbl
UNION
SELECT id, description, arrival_date FROM tools_tbl

is it correct?

How about this?

SELECT 'person' AS tb, id, name, arrival_date, '' AS description
FROM person_tbl
UNION
SELECT 'tools' AS tb, id, '' AS name, arrival_date, description
FROM tools_tbl

filippo wrote:
sample data:
1 filippo 5/5/2006 24
4 joe 5/21/2006 45
6 ed 5/6/2006 52

sample data
2 car 5/5/2006
3 tent 5/5/2006
3 bus 5/21/2006
4 trailer 5/21/2006
5 boat 5/21/2006
7 tent 5/6/2006

sample data ???

I cannot say based on the information prvided so far. I can only guess.
But why just ask us? Why don't you run that query and find out. Does it
give the results you want?

Don't be afraid of testing your code.
Ed

IF the ID's are unique and are mutually exclusive between the tables what
unique data element has them both? You have nothing to join them together.
You could make a querry up that is just a union of them both or one that is
list of records not in each other, which would be all of them.

It does no good to have person_name and car_description in the same table,
because one would always be blank. It is kind of an odd idea to have a
person id and a car id be part of the same basic generated key. Some
databases let you make tables like COFILES. You could call the Person table
and the Car table cofiles of one master object table.

For the id make a computed field that concatinates the two ID's. If they
are mutually exclusive the resulting field will alwasy be the ID you want.
It will either be null + id2 or id1 + Null.

"filippo" <filippo2991 (AT) virgilio (DOT) it> wrote