Solved

how to use join in the following sql queries

Posted on 2009-05-12
5
182 Views
Last Modified: 2012-05-06
Hi Experts,
How can i join on branch in the following two sql  queries.
Thanx

Select ve.Branch,count(ve.ID) 'No of Available Employees ' from  [193.0.9.223].HumanResources.dbo.vwEmployee ve

left join [193.0.9.223].HumanResources.dbo.vwBranch vb on ve.branch=vb.Name

where ve.leavingdate is null 

group by ve.branch order by ve.Branch
 
 

Select D.Branch,D.Employees 'No of Registered Employees' from (select A.Branch,count(A.EmployeeID)'Employees' from 

(select E.EmployeeID,E.Firstname,E.Lastname,B.Branch

from dbo.tblEmployee E

left join dbo.tblBranch B 

on E.BranchID=B.BranchID

left join [193.0.9.223].HumanResources.dbo.vwEmployee VE

on E.EmployeeID =VE.ID

where VE.LeavingDate is null

)A group by A.Branch )D order by D.Branch

Open in new window

0
Comment
Question by:mmalik15
  • 2
  • 2
5 Comments
 
LVL 6

Expert Comment

by:openshac
Comment Utility
first query:
left join dbo.tblBranch B on vb.BranchID=B.BranchID

second query is already on joined on tblBranch
0
 

Author Comment

by:mmalik15
Comment Utility
Both the queries return branch name and employees count. First query is returning data(2 columns Branch and No of Available employees) from HR database. Second query is returning from Training database (2 columns Branch and No of Registered Employees). What I am looking for is a query returning three columns; [Branch], [No of Available Employees], [No of Registered Employees] . Can i join them or union them in a way to get these columns
thanks
0
 
LVL 3

Expert Comment

by:alchemyst999
Comment Utility
See how this works for you:
SELECT 	RE.Branch, RE.Reg_Emp As 'No of Registered Employees', AE.AV_Emp As 'No of Available Employees'

FROM 	(SELECT ve.Branch, count(ve.ID) As 'No of Available Employees ' 

		FROM  	[193.0.9.223].HumanResources.dbo.vwEmployee As ve

				LEFT JOIN 

				[193.0.9.223].HumanResources.dbo.vwBranch As vb 

				ON ve.branch = vb.Name

		WHERE 	ve.leavingdate IS NULL

		GROUP BY ve.branch) As AE

		LEFT JOIN

		(SELECT B.Branch, COUNT(E.EmployeeID) As Reg_Emp

		FROM 	dbo.tblEmployee As E

				LEFT JOIN dbo.tblBranch As B

				ON E.BranchID = B.BranchID

				LEFT JOIN [193.0.9.223].HumanResources.dbo.vwEmployee As VE

				ON E.EmployeeID = VE.ID

		WHERE VE.LeavingDate IS NULL

		GROUP BY E.Branch) As RE

		ON AE.Branch = RE.Branch

Open in new window

0
 

Author Comment

by:mmalik15
Comment Utility
Thanks for above comment but i am still getting an error which i m trying to resolve for last two hours. The error i get is
Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.

I have made few changes and attached the sql

Any ideas

SELECT 	RE.Branch, RE.Reg_Emp As 'No of Registered Employees', AE.AV_Emp As 'No of Available Employees'

FROM 	(SELECT ve.Branch, count(ve.ID) As AV_Emp

		FROM  	[193.0.9.223].HumanResources.dbo.vwEmployee As ve

LEFT JOIN 

				[193.0.9.223].HumanResources.dbo.vwBranch As vb 

				ON ve.branch = vb.Name

		WHERE 	ve.leavingdate IS NULL

		GROUP BY ve.branch) As AE 

		LEFT JOIN 

		(SELECT B.Branch, COUNT(E.EmployeeID) As Reg_Emp

		FROM 	tblEmployee As E

				LEFT JOIN tblBranch As B

				ON E.BranchID = B.BranchID

				LEFT JOIN [193.0.9.223].HumanResources.dbo.vwEmployee As VE

				ON E.EmployeeID = VE.ID

		WHERE VE.LeavingDate IS NULL

		GROUP BY B.Branch) As RE

		ON  AE.Branch = RE.Branch

Open in new window

0
 
LVL 3

Accepted Solution

by:
alchemyst999 earned 500 total points
Comment Utility
I dont know too much about dB collation, but I think this issue is because two of the tables you are trying to join have different collation properties.

You can try adding COLLATE DATABASE_DEFAULT after each table reference in a FROM clause, e.g:

Problem is that it may not be all the joins that are causing the issue, so having it in the wrong place will also throw an error I think.  Best to play about with it until it works, but this *should* be the answer.
SELECT  RE.Branch, RE.Reg_Emp As 'No of Registered Employees', AE.AV_Emp As 'No of Available Employees'

FROM    (SELECT ve.Branch, count(ve.ID) As AV_Emp

                FROM    [193.0.9.223].HumanResources.dbo.vwEmployee As ve

								LEFT JOIN 

                                [193.0.9.223].HumanResources.dbo.vwBranch As vb 

                                ON ve.branch COLLATE DATABASE_DEFAULT = vb.Name COLLATE DATABASE_DEFAULT

                WHERE   ve.leavingdate IS NULL

                GROUP BY ve.branch) As AE 

                LEFT JOIN 

                (SELECT B.Branch, COUNT(E.EmployeeID) As Reg_Emp

                FROM    tblEmployee As E

                                LEFT JOIN tblBranch As B

                                ON E.BranchID COLLATE DATABASE_DEFAULT = B.BranchID COLLATE DATABASE_DEFAULT

                                LEFT JOIN [193.0.9.223].HumanResources.dbo.vwEmployee As VE

                                ON E.EmployeeID COLLATE DATABASE_DEFAULT = VE.ID COLLATE DATABASE_DEFAULT

                WHERE VE.LeavingDate IS NULL

                GROUP BY B.Branch) As RE

                ON  AE.Branch COLLATE DATABASE_DEFAULT = RE.Branch COLLATE DATABASE_DEFAULT

Open in new window

0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now