Need help for joining 3 tables with Group By..

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

Need help for joining 3 tables with Group By..

Author

Topic

abivenkat
Starting Member

8 Posts

Posted - 2012-04-19 : 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

190 Posts

Posted - 2012-04-25 : 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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources