By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,704 Members | 1,867 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,704 IT Pros & Developers. It's quick & easy.

query to combine two tables

P: n/a
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?
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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?

Nov 12 '05 #2

P: n/a
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?

Nov 12 '05 #3

P: n/a
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?

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.