query to combine two tables

[david liu]

here's what i want to have happen:

results_table:
id, val1, val2
1, 2, 1
2, 1, 0
3, 1, 0
4, 1, 1

the first query (make-table) generates the following ("" AS val2):

id, val1, val2
1, 2, 0
2, 1, 0
3, 1, 0

the second query (it's an append query) results in the following:

id, val1, val2
1, 2, 0
2, 1, 0
3, 1, 0
1, 0, 1
4, 0, 1

how do i achieve what i want in the results_table?

[Pavel Romashkin]

I thought at first that Result is supposed to have a number in place of
a zero from a superposition of tables 1 and 2, sort of like an "OR"
operation for each ID. But then you have a 4,1,1 in the result. I am
lost - how do you get that? There is no 4,1,0 record anywhere.
In other words, how the Result is achieved?

Pavel

david liu wrote:

here's what i want to have happen:

results_table:
id, val1, val2
1, 2, 1
2, 1, 0
3, 1, 0
4, 1, 1

the first query (make-table) generates the following ("" AS val2):

id, val1, val2
1, 2, 0
2, 1, 0
3, 1, 0

the second query (it's an append query) results in the following:

id, val1, val2
1, 2, 0
2, 1, 0
3, 1, 0
1, 0, 1
4, 0, 1

how do i achieve what i want in the results_table?

[david liu]

Pavel Romashkin <pa*************@hotmail.com> wrote in message news:<40***************@hotmail.com>...

I thought at first that Result is supposed to have a number in place of
a zero from a superposition of tables 1 and 2, sort of like an "OR"
operation for each ID. But then you have a 4,1,1 in the result. I am
lost - how do you get that? There is no 4,1,0 record anywhere.
In other words, how the Result is achieved?

oops. typo on row id=4. it should be 4, 0, 1.
Pavel

david liu wrote:

here's what i want to have happen:

results_table:
id, val1, val2
1, 2, 1
2, 1, 0
3, 1, 0
4, 1, 1

the first query (make-table) generates the following ("" AS val2):

id, val1, val2
1, 2, 0
2, 1, 0
3, 1, 0

the second query (it's an append query) results in the following:

id, val1, val2
1, 2, 0
2, 1, 0
3, 1, 0
1, 0, 1
4, 0, 1

how do i achieve what i want in the results_table?

[Pavel Romashkin]

SELECT ID, max(val1), max(val2) FROM (SELECT * FROM qry1 UNION SELECT *
FROM qry2) GROUP BY ID

should produce the result you show in the example. You need to figure
out what you want to happen if you have source records like

1, 2, 1
1, 1, 3

which is not part of your example.
Pavel

david liu wrote:

Pavel Romashkin <pa*************@hotmail.com> wrote in message news:<40***************@hotmail.com>...

I thought at first that Result is supposed to have a number in place of
a zero from a superposition of tables 1 and 2, sort of like an "OR"
operation for each ID. But then you have a 4,1,1 in the result. I am
lost - how do you get that? There is no 4,1,0 record anywhere.
In other words, how the Result is achieved?

oops. typo on row id=4. it should be 4, 0, 1.

Pavel

david liu wrote:

here's what i want to have happen:

results_table:
id, val1, val2
1, 2, 1
2, 1, 0
3, 1, 0
4, 1, 1

the first query (make-table) generates the following ("" AS val2):

id, val1, val2
1, 2, 0
2, 1, 0
3, 1, 0

the second query (it's an append query) results in the following:

id, val1, val2
1, 2, 0
2, 1, 0
3, 1, 0
1, 0, 1
4, 0, 1

how do i achieve what i want in the results_table?