How do I combine these two MS-SQL querries?

I have two querries that I need to combine.  There is not a one to one relationship for these queries when combined.  I am sure the solution is using inner or outer joins or left and right joins.  When I combine them, I get fewer rows that what I should (118 vs 393).
Anyway, the first query is:

SELECT e.employees_id, e.fname, e.lname, e.room, e.campusbox, e.phone, e.fax, e.email, e.url, e.image, e.bio, e.websiteURL, t.Titles, b.building, d.department
FROM dbo.Employees2 e
left join dbo.CBTitles t  on e.title_id = t.CBTitles_id
left join dbo.Building b  on e.building_id = b.building_id
left join dbo.Department d on e.department_id = d.department_id

The second query is:

SELECT e.employees_id, deg.degree_ID, deg.degree, ud.userDegree_ID, ud.Employees_ID, ud.degree_ID, ud.beginyear, ud.endyear, u.university_ID, u.universityName
FROM dbo.userDegrees ud
left join dbo.employees2 e on ud.Employees_ID = e.employees_ID
left join dbo.degree deg on ud.degree_ID = deg.degree_ID
left join dbo.universities u on ud.university_ID = u.university_ID                                    
Order by ud.endyear

I've combined them this way but this is where I get only 118 records.

SELECT e.employees_id, e.fname, e.lname, e.room, e.campusbox, e.phone, e.fax, e.email, e.url, e.image, e.bio, e.websiteURL, t.Titles, b.building, d.department,
deg.degree_ID, deg.degree, ud.userDegree_ID, ud.degree_ID, ud.beginyear, ud.endyear, u.university_ID, u.universityName
From employees2 e, cbtitles t, building b, department d, userDegrees ud, universities u, degree deg
Where ud.Employees_ID = e.employees_ID
and e.title_id = t.CBTitles_id
and e.building_id = b.building_id
and e.department_id = d.department_id
and ud.degree_ID = deg.degree_ID
and ud.university_ID = u.university_ID
Order by ud.endyear

I believe I've asked this type of question before but I don't remember the answer.  It was many years ago.
Jack SeamanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RayData AnalystCommented:
This really depends on the end result you are looking to get.

You may want to simply UNION ALL your two queries.  You'd likely need to add some empty columns and get columns of the same information/information type aligned.

OR

Here is the right way to join them assuming that employees2->employees_id is the same number that is used in userDegrees->Employees_ID
You'll also need to ensure your select statement is providing what you want in the desired Order.

PS you will not get the same number of results as running each query separately then adding the number of rows from each.  This is due to some data being combined into ONE row when you join everything.  The Union method would yield the total you expect (393).
Also to note, I did not look very thoroughly at your "combination" as joins using the where clause 'wear me out' trying to read them and match back to tables.  It could be essentially the same thing I did below.

As usual, your mileage may vary, but I hope this helps.

SELECT e.employees_id, e.fname, e.lname, e.room, e.campusbox, e.phone, e.fax, e.email, e.url, e.IMAGE, e.bio, e.websiteURL, t.Titles, b.building, d.department,deg.degree_ID, deg.degree, ud.userDegree_ID, ud.Employees_ID, ud.degree_ID, ud.beginyear, ud.endyear, u.university_ID, u.universityName
FROM dbo.Employees2 e
left join dbo.userDegrees ud on e.employees_id = ud.Employees_ID
LEFT JOIN dbo.CBTitles t ON e.title_id = t.CBTitles_id
LEFT JOIN dbo.Building b ON e.building_id = b.building_id
LEFT JOIN dbo.Department d ON e.department_id = d.department_id
LEFT JOIN dbo.degree deg ON ud.degree_ID = deg.degree_ID
LEFT JOIN dbo.universities u ON ud.university_ID = u.university_ID
ORDER BY ud.endyear

Open in new window

0
MlandaTCommented:
Try this:
with Employees as (

	SELECT e.employees_id, e.fname, e.lname, e.room, e.campusbox, e.phone, e.fax, e.email, e.url, e.image, e.bio, e.websiteURL, t.Titles, b.building, d.department 
	FROM dbo.Employees2 e
	left join dbo.CBTitles t  on e.title_id = t.CBTitles_id 
	left join dbo.Building b  on e.building_id = b.building_id
	left join dbo.Department d on e.department_id = d.department_id

), UserDegrees as (

	SELECT e.employees_id, deg.degree_ID, deg.degree, ud.userDegree_ID, ud.Employees_ID, ud.degree_ID, ud.beginyear, ud.endyear, u.university_ID, u.universityName
	FROM dbo.userDegrees ud
	left join dbo.employees2 e on ud.Employees_ID = e.employees_ID
	left join dbo.degree deg on ud.degree_ID = deg.degree_ID
	left join dbo.universities u on ud.university_ID = u.university_ID                                     

)
select Employees.*, UserDegrees.*
from Employees
	left outer join UserDegrees on Employees.employees_id = UserDegrees.employees_id
Order by UserDegrees.endyear

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jack SeamanAuthor Commented:
Ray's query seems to work.  I've had to run this with an XML extract.  After, the user sees the xml file and approves, I will close out the question.  

How do I assign points?
0
RayData AnalystCommented:
You say my solution was the working one then you close the question and mark MlandaT's answer as the solution.  What happened?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

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.