April 19, 2012 at 12:58 am
Hi all,
I have a requirement where I have to show the data’s in a grid from 3 different tables from database. The tables are CompanyDetails, UserDetails, CompanyVendorMapping
Columns of CompanyDetails are
a.CompanyId
b.CompanyName
c.CompanyContactNumber
d.DB_Name
Columns of UserDetails are
a.UserId
b.UserName
c.ContactNumber
d.IsActive
Columns of CompanyVendorMapping are
a.MappingId
b.CompanyId (Foreign Key reference)
c.UserId (Foreign Key reference)
Now in the grid, I have to show the columns like
CompanyName, CompanyContactNumber, DB_Name, Number of Active Users per company*.
In “Number of Active Users per company” column, I have to show the count of active users per company. I have to write the query with group by, but while using the group by, we have to specify all the columns in Group By which we are selecting; here the Column DB_Name have some duplicate values, where more than one company can have same DB. I am using entity framework architecture, so please help me writing the sql query or LINQ for this scenario. Thanks in advance
Thanks,
abivenkat
April 19, 2012 at 1:57 am
This was removed by the editor as SPAM
April 19, 2012 at 2:45 am
Given that the DB_Name will be duplicate the above solution wont work.
You have to do something like this: In the future, please provide some testdata..
EDIT: Sorry, the above solution is valid, and no need for extra CTE..
DECLARE @CompanyDetails TABLE
(CompanyId INT PRIMARY KEY,
CompanyName CHAR(50),
CompanyContactNumber CHAR(20),
DB_Name CHAR(10))
DECLARE @UserDetails TABLE
(UserId INT PRIMARY KEY,
UserName CHAR(50),
ContactNumber CHAR(20),
IsActive BIT)
DECLARE @CompanyVendorMapping TABLE
(MappingId INT PRIMARY KEY,
CompanyId INT,
UserId INT)
INSERT INTO @CompanyDetails
SELECT 1, 'A','123-456','DB1'
UNION
SELECT 2, 'B','123-567','DB2'
UNION
SELECT 3, 'C','123-899','DB1'
INSERT INTO @UserDetails
SELECT 1, 'USER1', '456-789',1
UNION
SELECT 2, 'USER2', '456-456',1
UNION
SELECT 3, 'USER3', '456-677',0
INSERT INTO @CompanyVendorMapping
SELECT 1, 1, 1
UNION
SELECT 2, 1, 2
UNION
SELECT 3, 1, 3
UNION
SELECT 4, 2, 1
UNION
SELECT 5, 2, 2
UNION
SELECT 6, 3, 1;
WITH CTETotalUsers(CompanyId, UserCount)
AS
(Select CD.CompanyId, COUNT(*) As UserCount
FROM @CompanyDetails CD
INNER JOIN @CompanyVendorMapping CVM
ON CD.CompanyId = CVM.CompanyId
INNER JOIN @UserDetails UD
ON CVM.UserId = UD.UserId
GROUP BY CD.CompanyId)
SELECT CD.CompanyName, CompanyContactNumber, DB_Name, Tu.UserCount
FROM @CompanyDetails CD
INNER JOIN CTETotalUsers TU
ON CD.CompanyId = TU.CompanyId
April 19, 2012 at 9:59 pm
hi,
thanks for the reply first of all...
i have to group by all the columns such as CompanyName, CompanyContactNumber etc., except the DB_Name where more than one company can have same DB_Name, so i have to avoid the column DB_Name adding in group by, so how should i do this?
thanks,
abivenkat
April 20, 2012 at 7:51 am
abivenkat (4/19/2012)
hi,thanks for the reply first of all...
i have to group by all the columns such as CompanyName, CompanyContactNumber etc., except the DB_Name where more than one company can have same DB_Name, so i have to avoid the column DB_Name adding in group by, so how should i do this?
thanks,
abivenkat
Could you provide some test records? Not sure I'm understanding your concern... Stewart's solution should work.
April 23, 2012 at 1:28 am
stevro (4/19/2012)
Given that the DB_Name will be duplicate the above solution wont work.You have to do something like this: In the future, please provide some testdata..
EDIT: Sorry, the above solution is valid, and no need for extra CTE..
DECLARE @CompanyDetails TABLE
(CompanyId INT PRIMARY KEY,
CompanyName CHAR(50),
CompanyContactNumber CHAR(20),
DB_Name CHAR(10))
DECLARE @UserDetails TABLE
(UserId INT PRIMARY KEY,
UserName CHAR(50),
ContactNumber CHAR(20),
IsActive BIT)
DECLARE @CompanyVendorMapping TABLE
(MappingId INT PRIMARY KEY,
CompanyId INT,
UserId INT)
INSERT INTO @CompanyDetails
SELECT 1, 'A','123-456','DB1'
UNION
SELECT 2, 'B','123-567','DB2'
UNION
SELECT 3, 'C','123-899','DB1'
INSERT INTO @UserDetails
SELECT 1, 'USER1', '456-789',1
UNION
SELECT 2, 'USER2', '456-456',1
UNION
SELECT 3, 'USER3', '456-677',0
INSERT INTO @CompanyVendorMapping
SELECT 1, 1, 1
UNION
SELECT 2, 1, 2
UNION
SELECT 3, 1, 3
UNION
SELECT 4, 2, 1
UNION
SELECT 5, 2, 2
UNION
SELECT 6, 3, 1;
WITH CTETotalUsers(CompanyId, UserCount)
AS
(Select CD.CompanyId, COUNT(*) As UserCount
FROM @CompanyDetails CD
INNER JOIN @CompanyVendorMapping CVM
ON CD.CompanyId = CVM.CompanyId
INNER JOIN @UserDetails UD
ON CVM.UserId = UD.UserId
GROUP BY CD.CompanyId)
SELECT CD.CompanyName, CompanyContactNumber, DB_Name, Tu.UserCount
FROM @CompanyDetails CD
INNER JOIN CTETotalUsers TU
ON CD.CompanyId = TU.CompanyId
This seems to work fine according to you description. What else ordering do you want to do??....Please be specific.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply