This discussion is archived
1 Reply Latest reply: Sep 29, 2013 8:57 PM by Madhu.149 RSS

Reg: Query with UNION

Madhu.149 Newbie
Currently Being Moderated

Dear All,

 

I have two tables

TABLE A (A_DATE, COLA1, COLA2)

TABLE B (B_DATE)

 

A_DATE = B_DATE

 

Sample tables:

 

TABLE A (11 rows selected)

A_DATECOLA1COLA2
01-03-201354VAL76
01-04-201311VAL78
01-04-201311VAL22
01-04-201374VAL22
02-04-201315VAL45
02-04-201311VAL22
03-04-201344VAL22
03-04-201371VAL73
04-04-201399VAL13
05-04-201311VAL22
05-04-201311VAL23

 

TABLE B (01 April to 30 April)

B_DATE
01-04-2013
02-04-2013
03-04-2013
04-04-2013

 

(condition 1: Month: April

AND

condition 2: CALA1 --> 11

AND

condition 3: COLA2 --> VAL22)

 

I have to display all the records for month April from TABLE A,

If records are not present in TABLE A for all 30 days, I need to display null, by fetching DATE information from TABLE B

 

Expected output (Sample)

DateCOLA1COLA2
01-04-201311VAL22
02-04-201311VAL22
03-04-2013
04-04-2013
05-04-201311VAL22

 

 

Currently I have the below query

 

select A_DATE, COLA1, COLA2 from TABLEA

where COLA1 = 11 and COLA2 in ('VAL22') and A_DATE between '2013-04-01 00:00:00.0' AND '2013-04-30 00:00:00.0'

union all

select B_DATE, null, null from TABLEB where date between '2013-04-01 00:00:00.0' AND '2013-04-30 00:00:00.0'

 

But I am getting duplicate records for the values present in TABLE A

 

Current output (sample)

DateCOLA1COLA2
01-04-201311VAL22
01-04-2013
02-04-201311VAL22
02-04-2013
03-04-2013
04-04-2013
05-04-201311VAL22
05-04-2013

 

 

I tried with both UNION and UNION ALL, but getting same result.

  • 1. Re: Reg: Query with UNION
    Ramin Hashimzadeh Expert
    Currently Being Moderated

    Hi,

    try this :

     

    select b.B_DATE, a,COLA1, a,COLA2

      from TABLEB b

     

      left join TABLEA a

      on a.A_DATE = b.B_DATE 

      and COLA1 = 11

      and COLA2 in ('VAL22')

     

    where B_DATE between to_date('2013-04-01','yyyy-mm-dd') AND to_date('2013-04-30','yyyy-mm-dd')

     

     

    ----

    Ramin Hashimzade

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points