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