7 Replies - 2958 Views - Last Post: 14 August 2017 - 09:00 AM Rate Topic: -----

#1 ikhlas06  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 178
  • Joined: 20-May 16

oracle left join remove duplicates

Posted 10 August 2017 - 01:36 PM

I am using oracle



a_table
|-----|-----|-------------|
| key | cat | t_date      | 
|-----|-----|--------------|
| 3   | new | 1/01/2012   |
| 2   | new | 1/11/2013   |
| 4   | new |             |
| 5   | old |             |
|--------------------------|



b_table
|-----|-------------|
| key | start_date  | 
|-----|-------------|
| 4   | 1/01/2012   |
| 4   | 1/11/2013   |
| 9   |             |
| 10  | 3/22/2017   |
|-------------------|


code:

select distinct
a.cat,
sum(case when a.t_date is null then 1 else 0 end),
sum(case when b.start_date is null then 1 else 0 end),
from a_table a 
left join b_table b on b.key = a.key
group by a.name, a.t_date, b.start_date 


This is counting key 4 inside b_table twice. which make sence bc there are two records with key 4 (b_table).

What I want:
- Even though I am 'left join' b_table onto a_table... I dont want dupulte records (key 4 inside b_table)
- I want to count 1 inside b_table. even though b_table has two key 4.


what I tried:
I tried left outter join but no luck...
I also tried inner join but no luck
Is there a function I can use inside left join so that it wont count double record key inside b_table?

This post has been edited by ikhlas06: 10 August 2017 - 01:51 PM


Is This A Good Question/Topic? 0
  • +

Replies To: oracle left join remove duplicates

#2 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 13493
  • View blog
  • Posts: 53,899
  • Joined: 12-June 08

Re: oracle left join remove duplicates

Posted 10 August 2017 - 01:53 PM

First question - why are there duplicate keys?

Second - in table B - which date do you want? Min? Max?
Was This Post Helpful? 1
  • +
  • -

#3 ikhlas06  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 178
  • Joined: 20-May 16

Re: oracle left join remove duplicates

Posted 11 August 2017 - 06:07 AM

View Postmodi123_1, on 10 August 2017 - 01:53 PM, said:

First question - why are there duplicate keys?

Second - in table B - which date do you want? Min? Max?



1. a_table key is primary key. b_table key is foregin key
2. either. I want count
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 13493
  • View blog
  • Posts: 53,899
  • Joined: 12-June 08

Re: oracle left join remove duplicates

Posted 11 August 2017 - 06:47 AM

Table b - do the count in a sub query.
Table a - join off this sub query.
Was This Post Helpful? 1
  • +
  • -

#5 ikhlas06  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 178
  • Joined: 20-May 16

Re: oracle left join remove duplicates

Posted 11 August 2017 - 11:03 AM

I am not sure how to do that. Can I see a example ?
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 13493
  • View blog
  • Posts: 53,899
  • Joined: 12-June 08

Re: oracle left join remove duplicates

Posted 11 August 2017 - 11:13 AM

sure.. off the top of my head here is an example without doing it for you:

cr eate table #foo(lval int, sval varchar(10))
cre ate table #bar(lval int, sval varchar(2))


ins ert into #foo(lval, sval) values (1, 'one')
ins ert into #foo(lval, sval) values (2, 'two')
ins ert into #foo(lval, sval) values (3, 'three')

in sert into #bar(lval, sval) values (1, 'ab')
inse rt into #bar(lval, sval) values (1, 'cd')
ins ert into #bar(lval, sval) values (3, 'ef')


SELECT *
from #foo a
join ( SELECT lval, sval
	   from #bar
) b on a.lval = b.lval

dr op table #foo
dr op table #bar


lval        sval       lval        sval
----------- ---------- ----------- ----
1           one        1           ab
1           one        1           cd
3           three      3           ef

Was This Post Helpful? 1
  • +
  • -

#7 ikhlas06  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 178
  • Joined: 20-May 16

Re: oracle left join remove duplicates

Posted 14 August 2017 - 08:45 AM

thanks for the idea. This worked!

SELECT DISTINCT *
from #foo a
join ( SELECT DISTINCT lval, sval
	   from #bar
) b on a.lval = b.lval

This post has been edited by ikhlas06: 14 August 2017 - 08:45 AM

Was This Post Helpful? 0
  • +
  • -

#8 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 13493
  • View blog
  • Posts: 53,899
  • Joined: 12-June 08

Re: oracle left join remove duplicates

Posted 14 August 2017 - 09:00 AM

Good to hear. I wold probably have thrown a min/max for consistency, but so it goes.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1