Mario
Mario

Reputation: 13

SQL query with union

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

Answers (4)

spencer7593
spencer7593

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

sgeddes
sgeddes

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

SQL Fiddle Demo

Results:

DATEFACT         IDA   IDB
March, 01 2013   88    88
March, 02 2013   89    89
March, 03 2013   90    90

Upvotes: 1

Gordon Linoff
Gordon Linoff

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

John Woo
John Woo

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

Related Questions