-
Feb 27th, 2014, 11:35 PM
#1
Thread Starter
Lively Member
How to group by a table based on another table
I have fee groups and under that fee groups, there are fee types. When there are entries of all the fee types under a group, the group name must be displayed. How can I achieve this?
Example There is a fee group f1 with feegroupid 1. under that fee group, in another table there are 2 types of fee types ft1,ft2. If both ft1 and ft2 is present in the result table, group them and they should be displayed as f1
-
Feb 28th, 2014, 03:59 AM
#2
Re: How to group by a table based on another table
You join the tables together using an inner join and then you can group by any field in the comined set.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
Feb 28th, 2014, 07:19 AM
#3
Re: How to group by a table based on another table
Do you mean something like this? MS SQL 2005
Code:
create table ##FeeGroups(FeeGroupID int, FeeType Char(3))
insert into ##FeeGroups(FeeGroupID,FeeType) Values(1,'ft1')
insert into ##FeeGroups(FeeGroupID,FeeType) Values(2,'ft2')
insert into ##FeeGroups(FeeGroupID,FeeType) Values(3,'ft3')
create table ##FeeTypes(FeeType Char(3))
insert into ##FeeTypes(FeeType) Values('ft1')
insert into ##FeeTypes(FeeType) Values('ft2')
insert into ##FeeTypes(FeeType) Values('ft3')
insert into ##FeeTypes(FeeType) Values('ft4')
select FeeType from
(select
case when b.FeeType = 'ft1' or b.FeeType = 'ft2' then 'ft1'
else b.feetype
end as 'FeeType'
from ##FeeGroups a
join ##FeeTypes b on a.Feetype = b.Feetype) as Temp
group by FeeType
drop table ##FeeGroups
drop table ##FeeTypes
Notice ft4 was not matched and is not in the results.
Last edited by TysonLPrice; Feb 28th, 2014 at 07:32 AM.
Please remember next time...elections matter!
-
Feb 28th, 2014, 07:28 AM
#4
Thread Starter
Lively Member
Re: How to group by a table based on another table
Its something like this:
First create groups
create table feegroups(feegroupid int,feegroupname varchar)
insert feegroups values(1,'SEMESTER1')
Next:
create table feetypes(feetypeid int, feegroupid int,feename varchar)
insert feegroups values(1,1,'FIRSFEE')
insert feegroups values(2,1,'SECONDFEE')
insert feegroups values(3,1,'THIRDFEE')
Note the feegroupid value comes from first table (SEMESTER1'S ID)
Now while paying fees, the entry goes like this
create table feespayment(studentid int,feetypeid int)
insert feespayment values(45,1)
insert feespayment values(45,2)
insert feespayment values(45,3)
-A student with Id 45 has paid all the fees under 'semester1'
So if a student pays all the 3 feetypes under the feegroup 'SEMESTER1', then while selecting from the feespayment (report), 'SEMESTER1' should be displayed instead of all the feetype names
like :
STUDENT45,SEMESTER1
-
Feb 28th, 2014, 12:16 PM
#5
Addicted Member
Re: How to group by a table based on another table
Code:
select distinct(fp.studentid), fg.feegroupname
from feeGroups as fg inner join feeTypes ft
on fg.feeGroupID = ft.feeGroupID
inner join feesPayment fp
on fp.feeTypeID = ft.feeTypeID
where fp.feeTypeID in (1, 2, 3)
and fg.feegroupid = 1
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|