Reputation: 13
I have below query. I did union to get desired output but I am not getting it.
create table foo (dateFact date, id int);
create table bar (dateFact date, id int);
insert into foo(dateFact, id) values('2013-03-01', 88), ('2013-03-02', 89),('2013-03-03', 90);
insert into bar(dateFact, id) values('2013-03-01', 88), ('2013-03-02', 89),('2013-03-03', 90);
select * from foo;
select * from bar;
(select dateFact, id as idA, 0 from foo order by dateFact) union (select dateFact, 0, id as idB from bar order by dateFact);
Above query gives me below result:
dateFact idA 0
2013-03-01 88 0
2013-03-02 89 0
2013-03-03 90 0
2013-03-01 0 88
2013-03-02 0 89
2013-03-03 0 90
But I want:
dateFact idA idB
2013-03-01 88 88
2013-03-02 89 89
2013-03-03 90 90
Thank you for the help!
Upvotes: 1
Views: 92
Reputation: 108400
There are several different queries which will return your specified resultset, given the example data. But the differences in the queries will become apparent when the rows in the tables have different values, different cardinalities, different numbers of rows, etc.
Different data is need to show the differences in the queries.
As one example, given the rows in tables foo
and bar
shown in the question, this query will also return the specified resultset:
SELECT f.dateFact
, f.id AS idA
, b.id AS idB
FROM foo f
JOIN bar b
ON f.dateFact = b.dateFact
ORDER BY f.dateFact
But, if the contents of the tables is different, the resultset from this query will be much different from some of the queries given in other answers.
(I'm trying to point out that the question does not provide sufficient information, to make a determination as to what query is the "right" query.)
Which query you choose to use really depends on what resultset you want when, for example, there is no "matching" row, in either the foo or bar tables, or when the same dateFact value appears in more than one row in the foo and/or bar tables, and so on.
Upvotes: 0
Reputation: 62841
Seems like you could use MAX
and CASE
with a CROSS JOIN
:
select f.dateFact,
max(case when f.dateFact = b.dateFact then f.id end) IDA,
max(case when f.dateFact = b.dateFact then b.id end) IDB
from foo f cross join bar b
group by f.dateFact
Results:
DATEFACT IDA IDB
March, 01 2013 88 88
March, 02 2013 89 89
March, 03 2013 90 90
Upvotes: 1
Reputation: 1269803
I'm not sure why you are surprised by the result. To get what you want, you need a group by
:
select dateFact, max(idA), max(idB)
from ((select dateFact, id as idA, 0
from foo
) union all
(select dateFact, 0, id as idB
from bar
order by dateFact
)
) t
group by dateFact
In this case union all
is preferable to union
, because union
removes duplicates and that is not needed.
Upvotes: 0
Reputation: 263723
Wrap the unioned query and perform aggregation on the outer query using MAX()
and grouped them by dateFact
.
SELECT dateFact, MAX(idA) idA, MAX(idB) idB
FROM
(
SELECT dateFact, id AS idA, 0 AS idB FROM foo
UNION
SELECT dateFact, 0 AS idA, id AS idB FROM bar
) s
GROUP BY dateFact
ORDER BY dateFact
Upvotes: 1