| Author |
 Topic  |
|
|
abivenkat
Starting Member
India
8 Posts |
 Posted - 04/19/2012 : 02:55:22
|
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
|
|
|
sql-programmers
Posting Yak Master
USA
189 Posts |
Posted - 04/25/2012 : 07:58:39
|
Try this script,
CREATE TABLE #CompanyDetails(CompanyId INT,CompanyName VARCHAR(100),CompanyContactNumber VARCHAR(20),DB_NAME VARCHAR(50))
CREATE TABLE #UserDetails(UserId INT,UserName VARCHAR(100),ContactNumber VARCHAR(20),IsActive BIT)
CREATE TABLE #CompanyVendorMapping(MappingId INT,CompanyId INT,UserId INT)
INSERT INTO #CompanyDetails( CompanyId , CompanyName , CompanyContactNumber , DB_NAME ) VALUES ( 1 , 'XXX','316537','DB1')
INSERT INTO #CompanyDetails( CompanyId , CompanyName , CompanyContactNumber , DB_NAME ) VALUES ( 2 , 'YYY','6464','DB1')
INSERT INTO #CompanyDetails( CompanyId , CompanyName , CompanyContactNumber , DB_NAME ) VALUES ( 3 , 'ZZZ','8989','DB2')
INSERT INTO #UserDetails( UserId , UserName , ContactNumber , IsActive )VALUES ( 1 , 'AAA','8989',1)
INSERT INTO #UserDetails( UserId , UserName , ContactNumber , IsActive )VALUES ( 2 , 'BBB','8989',1)
INSERT INTO #UserDetails( UserId , UserName , ContactNumber , IsActive )VALUES ( 3 , 'CCC','8989',1)
INSERT INTO #UserDetails( UserId , UserName , ContactNumber , IsActive )VALUES ( 4 , 'DDD','8989',0)
INSERT INTO #CompanyVendorMapping(MappingId,CompanyId,UserId) VALUES(1,1,1)
INSERT INTO #CompanyVendorMapping(MappingId,CompanyId,UserId) VALUES(2,1,2)
INSERT INTO #CompanyVendorMapping(MappingId,CompanyId,UserId) VALUES(3,1,3)
INSERT INTO #CompanyVendorMapping(MappingId,CompanyId,UserId) VALUES(4,2,1)
INSERT INTO #CompanyVendorMapping(MappingId,CompanyId,UserId) VALUES(5,2,2)
SELECT CompanyName, CompanyContactNumber, DB_Name,COUNT(#UserDetails.UserId) FROM #CompanyDetails LEFT JOIN #CompanyVendorMapping ON #CompanyDetails.CompanyId = #CompanyVendorMapping.CompanyId
LEFT JOIN #UserDetails ON #CompanyVendorMapping.UserId = #UserDetails.UserId AND #UserDetails.IsActive = 1
GROUP BY CompanyName, CompanyContactNumber, DB_Name
DROP TABLE #CompanyDetails
DROP TABLE #UserDetails
DROP TABLE #CompanyVendorMapping
SQL Server Programmers and Consultants
http://www.sql-programmers.com/ |
 |
|
| |
Topic |
|
|
|
Need help for joining 3 tables with Group By..
