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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
How do I assign points?
You say my solution was the working one then you close the question and mark MlandaT's answer as the solution. What happened?
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.
Open in new window