8 Replies - 795 Views - Last Post: 15 April 2016 - 09:13 AM

#1 vibinvictoria  Icon User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 103
  • Joined: 16-July 08

Right join with group by clause

Posted 14 April 2016 - 11:36 AM

Hi Expert,

Please share me your input on below query

I have two dataset.

set 1:

empid-Qtr-salary
1-Q3FY16-2000
1-Q2FY16-1000
2-Q3FY16-2000
2-Q2FY16-3000
2-Q1FY16-4000
2-Q4FY15-5000
3-Q2FY16-7000
3-Q1FY16-8000

set 2
id-qtr
1-Q3FY16
2-Q2FY16
3-Q1FY16
4-Q4FY15

I need to join set1 and set 2 and diaplay all the quarters with empid ,qtr and salary.

EXPECTED OUTPUT IS

1-Q3FY16-2000
1-Q2FY16-1000
1-Q1FY16-NULL
1-Q4FY15-NULL
2-Q3FY16-2000
2-Q2FY16-3000
2-Q1FY16-4000
2-Q4FY15-5000
3-Q3FY16-NULL
3-Q2FY16-3000
3-Q1FY16-7000
3-Q4FY15-NULL

tried with right out join . No luck.
select
from set1 s1
right join set2 s2 on s2.qtr=s1.qtr.

Is This A Good Question/Topic? 0
  • +

Replies To: Right join with group by clause

#2 andrewsw  Icon User is offline

  • say what now
  • member icon

Reputation: 6409
  • View blog
  • Posts: 25,904
  • Joined: 12-December 12

Re: Right join with group by clause

Posted 14 April 2016 - 11:45 AM

select ??

You haven't written what columns you want to return.
Was This Post Helpful? 0
  • +
  • -

#3 vibinvictoria  Icon User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 103
  • Joined: 16-July 08

Re: Right join with group by clause

Posted 14 April 2016 - 11:48 AM

select s1.empid,s2.qtr,s1.salary from set1 s1
right join set2 s2 on s2.qtr=s1.qtr.
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is offline

  • say what now
  • member icon

Reputation: 6409
  • View blog
  • Posts: 25,904
  • Joined: 12-December 12

Re: Right join with group by clause

Posted 14 April 2016 - 11:57 AM

Okay, so does that work?
Was This Post Helpful? 0
  • +
  • -

#5 vibinvictoria  Icon User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 103
  • Joined: 16-July 08

Re: Right join with group by clause

Posted 14 April 2016 - 12:08 PM

I didn't get expected result

Also i tried with group by clause. No luck

select s1.empid,s2.qtr,s1.salary from set1 s1
right join set2 s2 on s2.qtr=s1.qtr
group by s1.empid,s2.qtr,s1.salary

expected output is

1-Q3FY16-2000
1-Q2FY16-1000
1-Q1FY16-NULL
1-Q4FY15-NULL
2-Q3FY16-2000
2-Q2FY16-3000
2-Q1FY16-4000
2-Q4FY15-5000
3-Q3FY16-NULL
3-Q2FY16-3000
3-Q1FY16-7000
3-Q4FY15-NULL
Was This Post Helpful? 0
  • +
  • -

#6 andrewsw  Icon User is offline

  • say what now
  • member icon

Reputation: 6409
  • View blog
  • Posts: 25,904
  • Joined: 12-December 12

Re: Right join with group by clause

Posted 14 April 2016 - 12:19 PM

The expected output of 3-Q1FY16-7000 seems wrong to me. In which case, there is no GROUP BY required, you're just looking at joining the two tables.

What makes you think GROUP BY is required? This is typically used when you want to count or sum across groups.
Was This Post Helpful? 0
  • +
  • -

#7 vibinvictoria  Icon User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 103
  • Joined: 16-July 08

Re: Right join with group by clause

Posted 14 April 2016 - 06:43 PM

with in a group , it will apply right join and unmatched column from right table will display.But it doesn't work.
How can i get my expected output.
Was This Post Helpful? 0
  • +
  • -

#8 andrewsw  Icon User is offline

  • say what now
  • member icon

Reputation: 6409
  • View blog
  • Posts: 25,904
  • Joined: 12-December 12

Re: Right join with group by clause

Posted 15 April 2016 - 12:21 AM

Forget about grouping and group by for the moment, you are just trying to join two tables. Post you attempt to join the two tables together with details of any error you get.

Also, how are you studying SQL? Do you have a book?
Was This Post Helpful? 0
  • +
  • -

#9 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon


Reputation: 6996
  • View blog
  • Posts: 14,635
  • Joined: 16-October 07

Re: Right join with group by clause

Posted 15 April 2016 - 09:13 AM

There are no right joins or group bys here. Actually, I never, ever, use right joins, but you don't need one now, either.

First, you have a very non databasey thing to do: you want all values of one table and all values of another. This is called a FULL join.

From the employee table, you want (1,2,3) and from the quarter table you want (Q3FY16,Q2FY16,Q1FY16,Q4FY15). You'll want a sub query that 1,2,3. That will get you:
empid       qtr
----------- --------
1           Q1FY16
1           Q2FY16
1           Q3FY16
1           Q4FY15
2           Q1FY16
2           Q2FY16
2           Q3FY16
2           Q4FY15
3           Q1FY16
3           Q2FY16
3           Q3FY16
3           Q4FY15



Once you have that, you need only do a LEFT outer join to the employee table.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1