Improve company productivity with a Business Account.Sign Up

x
?
Solved

how to use join in the following sql queries

Posted on 2009-05-12
5
Medium Priority
?
210 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
ID: 24362842
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
ID: 24362911
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
ID: 24363571
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
ID: 24364596
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 2000 total points
ID: 24365937
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

608 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