February 10, 2009 at 12:33 pm
Hello,
I am trying to write a query that will select a contacts name, contact type, anniversary date and group membership of certain individuals in a database. I also wish to know what dept. the person that originally created them into the system works in.
With the query below is that the only way I can get the dept. of the person that entered them into the system is to reference the a.creatinguserid against the d.userid and then reference the d.deptcode against e.deptcode where they match. To be exact, I take the creating userid from the contact table and match (join) it with the userid in the users table. The record in the users table contains a deptid which I then match that to the same deptid in the deptlist table which allows me to get the actual dept. name. (I didn’t design this)
The problem is that some of the users no longer work here and they have been deleted from the users table. So when I go to reference a.creatinguserid against d.userid not all of the a.creatinguserid(s) find a match in the d.userid field and as a result those records are not returned.
What is the procedure to reference multiple tables together like this and still get a return even though all INNER JOIN conditions are not met? In other words, I still want some of the data for a given record returned even if all of the JOIN conditions don't exist. (Return all other requested info from a record even if the a.groupid field is NULL for a particular record).
Also some of the contacts have multiple anniversary dates due to some data entry fumbling. I wish to be able to combine the multiple dates into a single field in the return if possible rather than have 2 records returned for the same person, each one with a different anniversary date. In other words I want each record returned to be unique as far as their name goes but I still want to be aware of any multiple anniversary dates and also for the sake of having an accurate total count of non-duplicate records returned.
Sorry for the length.
SELECT a.contactid, a.firstname, a.middlename, a.lastname, a.contacttype, b.anniversarydate, c.groupname, a.creatinguserid, d.fullname, e.deptname
FROM contact a
INNER JOIN contactanniversarydate b
ON a.contactid=b.contactid
INNER JOIN group c
ON a.groupid=c.groupid
INNER JOIN users d
ON a.creatinguserid = d.userid
INNER JOIN deptlist e
ON d.deptcode = e.deptcode
GROUP BY a.contactid, a.firstname, a.middlename, a.lastname, a.deptid, a.contacttype, b.anniversarydate, c.deptname, a.userid, d.fullname, e.groupname
ORDER BY a.lastname
February 10, 2009 at 12:59 pm
perrydwill (2/10/2009)
Hello,I am trying to write a query that will select a contacts name, contact type, anniversary date and group membership of certain individuals in a database. I also wish to know what dept. the person that originally created them into the system works in.
With the query below is that the only way I can get the dept. of the person that entered them into the system is to reference the a.creatinguserid against the d.userid and then reference the d.deptcode against e.deptcode where they match. To be exact, I take the creating userid from the contact table and match (join) it with the userid in the users table. The record in the users table contains a deptid which I then match that to the same deptid in the deptlist table which allows me to get the actual dept. name. (I didn’t design this)
The problem is that some of the users no longer work here and they have been deleted from the users table. So when I go to reference a.creatinguserid against d.userid not all of the a.creatinguserid(s) find a match in the d.userid field and as a result those records are not returned.
What is the procedure to reference multiple tables together like this and still get a return even though all INNER JOIN conditions are not met? In other words, I still want some of the data for a given record returned even if all of the JOIN conditions don't exist. (Return all other requested info from a record even if the a.groupid field is NULL for a particular record).
Also some of the contacts have multiple anniversary dates due to some data entry fumbling. I wish to be able to combine the multiple dates into a single field in the return if possible rather than have 2 records returned for the same person, each one with a different anniversary date. In other words I want each record returned to be unique as far as their name goes but I still want to be aware of any multiple anniversary dates and also for the sake of having an accurate total count of non-duplicate records returned.
Sorry for the length.
SELECT a.contactid, a.firstname, a.middlename, a.lastname, a.contacttype, b.anniversarydate, c.groupname, a.creatinguserid, d.fullname, e.deptname
FROM contact a
INNER JOIN contactanniversarydate b
ON a.contactid=b.contactid
INNER JOIN group c
ON a.groupid=c.groupid
INNER JOIN users d
ON a.creatinguserid = d.userid
INNER JOIN deptlist e
ON d.deptcode = e.deptcode
GROUP BY a.contactid, a.firstname, a.middlename, a.lastname, a.deptid, a.contacttype, b.anniversarydate, c.deptname, a.userid, d.fullname, e.groupname
ORDER BY a.lastname
Change your last two INNER joins to LEFT OUTER joins.
February 10, 2009 at 1:05 pm
The way I handle that kind of chain is keep the set of tables that might have a "broken chain" in a derived table (I use CTEs for it now in 2005, but it's the same concept), and then do a left-join to that CTE or derived table.
Would look something like:
;WITH
UserDept (UserID, FullName, DeptName) as -- CTE for Users and Depts
(select userid, fullname, deptname
from dbo.users
inner join dbo.deptlist
on users.deptcode = deptlist.deptcode)
SELECT a.contactid, a.firstname, a.middlename, a.lastname, a.contacttype,
b.anniversarydate, c.groupname, a.creatinguserid, d.fullname, e.deptname
FROM contact a
INNER JOIN contactanniversarydate b
ON a.contactid=b.contactid
INNER JOIN group c
ON a.groupid=c.groupid
LEFT OUTER JOIN UserDept u
ON a.creatinguserid = u.userid
ORDER BY a.lastname;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 10, 2009 at 1:44 pm
Augh, I can't believe it was that simple. Thank you both!
Any idea on how the query could combine the multiple anniversary dates into a single field so that I don't get the multiple records with the same names returned?
Also some of the contacts have multiple anniversary dates due to some data entry fumbling. I wish to be able to combine the multiple dates into a single field in the return if possible rather than have 2 records returned for the same person, each one with a different anniversary date. In other words I want each record returned to be unique as far as their name goes but I still want to be aware of any multiple anniversary dates and also for the sake of having an accurate total count of non-duplicate records returned.
February 11, 2009 at 7:17 am
I'd probably do that one with another CTE and use the Max function on the date (if you want the last one) or Min (if you want the first one).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply