SQL Query Help

[cwwilly]

Thank you for taking the time to look at my question. Any help would
be much appreciated !

Database:
Microsoft SQL Server 2000

Database Structure:
1. tblEmployee
a. EmployeeID

2. tblCertification1
a. VisitDate
b. CertificationScore1

3. tblCertification2
a. VisitDate
b. CertificationScore2

Desired Result:
I want to list out each EmployeeID and add include the score from each
certification based on the VisitDate

EmployeeID | VisitDate | CertificationScore1 | CertificationScore2
1 | 02/02/05 | 50 | 50

If they didn't take Certification2 then leave it NULL

EmployeeID | VisitDate | CertificationScore1 | CertificationScore2
1 | 02/02/05 | 50 | <NULL>

Question:
I'm having difficulty linking the tables together and appending the
fields to the right of the EmployeeID based on their VisitDate. Any
ideas ?

Thank you so much !

cw*****@gmail.com

[David Rawheiser]

<cw*****@gmail.com> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...

Thank you for taking the time to look at my question. Any help would
be much appreciated !

Database:
Microsoft SQL Server 2000

Database Structure:
1. tblEmployee
a. EmployeeID

2. tblCertification1
a. VisitDate
b. CertificationScore1

3. tblCertification2
a. VisitDate
b. CertificationScore2

Desired Result:
I want to list out each EmployeeID and add include the score from each
certification based on the VisitDate

EmployeeID | VisitDate | CertificationScore1 | CertificationScore2
1 | 02/02/05 | 50 | 50

If they didn't take Certification2 then leave it NULL

EmployeeID | VisitDate | CertificationScore1 | CertificationScore2
1 | 02/02/05 | 50 | <NULL>

Question:
I'm having difficulty linking the tables together and appending the
fields to the right of the EmployeeID based on their VisitDate. Any
ideas ?

Thank you so much !

cw*****@gmail.com

I am assuming that you didn't list the employee key that should be on both
certificaiton tables.

select *
from Employee e
left join certificaiton1 c1 on e.EmployeeID = c1.EmployeeID
left join certificaiton2 c2 on e.EmployeeID = c2.EmployeeID

If you don't want to see employees that don't have any certificaitons add
the following:

where c1.VisitDate is not null or c2.VisitDate is not null

[cwwilly]

Thanks for your help. But this doesn't get me where I want to be.

My database structure is the following:
Database Structure:
1. tblEmployee
a. EmployeeID

2. tblCertification1
a. EmployeeID
b. VisitDate
c. CertificationScore1

3. tblCertification2
a. EmployeeID
b. VisitDate
c. CertificationScore2

I did accidently leave out the EmployeeID fiield in my post above.

The solution you gave me doesn't group by EmployeeID and VisitDate.
The employee will have to take two certification test on the same day
and I want both scores to show up on the same line per that employee
and date.

Thanks again for helping me out with this ! I really do appreciate it.

cw*****@gmail.com

[--CELKO--]

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

You might also want to learn the ISO-11179 Standards for data element
names, so you don't have those silly "tbl-" prefixes, etc.

The employee will have to take two certification test on the same

day and I want both scores to show up on the same line per that
employee and date. <<

CREATE TABLE Personnel -- collective or plural table names
(emp_id CHAR(9) NOT NULL PRIMARY KEY,
..);

Based on your specs, the two tests are attributes of the test event.
Therefore, you should not split them into separate tables.

CREATE TABLE CertificationTests
(emp_id CHAR(9) NOT NULL
REFERENCES Personnel(emp_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
test_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
certification_1_score INTEGER DEFAULT 0 NOT NULL,
certification_2_score INTEGER DEFAULT 0 NOT NULL,
PRIMARY KEY (emp_id, test_date));

[Erland Sommarskog]

(cw*****@gmail.com) writes:

Thanks for your help. But this doesn't get me where I want to be.

My database structure is the following:
Database Structure:
1. tblEmployee
a. EmployeeID

2. tblCertification1
a. EmployeeID
b. VisitDate
c. CertificationScore1

3. tblCertification2
a. EmployeeID
b. VisitDate
c. CertificationScore2

I did accidently leave out the EmployeeID fiield in my post above.

The solution you gave me doesn't group by EmployeeID and VisitDate.
The employee will have to take two certification test on the same day
and I want both scores to show up on the same line per that employee
and date.

For this kind if questions, it's always a good idea to post:

o CREATE TABLE statements for your tables (possibly simplified)
o INSERT statements with sample data.
o The desired output given the sample.

This makes it easy for anyone who is trying your problem to easy cut
and paste into to Query Analyzer, and thus you will get a tested query
in response.

The below may address your problem:

SELECT e.EmployeeID, f.VisitDate, f.Score1, f.Score2
FROM tblEmployee e
JOIN (SELECT EmployeeID = coalesce(c1.EmployeeID, c2.EmployeeID),
VisitDate = coalesce(c1.VisitDate, c2.VisitDate),
Score1 = c1.CertificactionScore1,
Score2 = c1.CertificactionScore2
FROM tblCertification1 c1
FULL JOIN tblCertification2 c2
ON c1.EmployeeID = c2.EmployeeID
AND c1.VisitDate = c2.VisitDate) AS f
ON e.EmployeeID = f.EmployeeID

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[cwwilly]

Thank you so much ! You guys rock. I got it figured out.

[cwwilly]

Erland,

One more question to go along with your previous post.

How would I add another table with another score to this query ? Such
as CertificationScore3 ?

Thanks,

cwwilly

[Hugo Kornelis]

On 3 Mar 2005 10:36:54 -0800, cw*****@gmail.com wrote:

How would I add another table with another score to this query ? Such
as CertificationScore3 ?

Hi cwwilly,

First, you should reconsider your design. It sounds as if you should
have just one table for the certifications. Something like the below
(note that most datatypes are based on assumptions)

CREATE TABLE Certifications
(CertificationNo int NOT NULL
,EmployeeID int NOT NULL
,VisitDate smalldatetime NOT NULL
,CertificationScore smallint
,PRIMARY KEY (CertificationNo, EmployeeID, VisitDate)
,FOREIGN KEY (EmployeeID) REFERENCES Employees
,CHECK (CertificationNo BETWEEN 1 AND 3)
)
For a kludge to get the desired results in the current situation, you
can extend the query posted by Erland to something like this:

SELECT e.EmployeeID, f.VisitDate, f.Score1, f.Score2, f.Score3
FROM tblEmployee e
JOIN (SELECT EmployeeID = coalesce(c1.EmployeeID, c2.EmployeeID,
c3.EmployeeID),
VisitDate = coalesce(c1.VisitDate, c2.VisitDate,
c3.VisitDate),
Score1 = c1.CertificactionScore1,
Score2 = c2.CertificactionScore2,
Score3 = c3.CertificactionScore3
FROM tblCertification1 c1
FULL JOIN tblCertification2 c2
ON c2.EmployeeID = c1.EmployeeID
AND c2.VisitDate = c1.VisitDate
FULL JOIN tblCertification3 c3
ON c3.EmployeeID = COALESCE (c1.EmployeeID, c2.EmployeeID)
AND c3.VisitDate = COALESCE (c1.VisitDate, c2.VisitDate))
AS f
ON e.EmployeeID = f.EmployeeID

The above is untested, since you didn't follow Erland's advise to post
CREATE TABLE and INSERT statements and desired output.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[cwwilly]

Thanks Hugo ! It worked like a charm. I really appreciate you helping
me with this.

cwwilly

[Erland Sommarskog]

Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes:

SELECT e.EmployeeID, f.VisitDate, f.Score1, f.Score2, f.Score3
FROM tblEmployee e
JOIN (SELECT EmployeeID = coalesce(c1.EmployeeID, c2.EmployeeID,
c3.EmployeeID),
VisitDate = coalesce(c1.VisitDate, c2.VisitDate,
c3.VisitDate),
Score1 = c1.CertificactionScore1,
Score2 = c2.CertificactionScore2,
Score3 = c3.CertificactionScore3
FROM tblCertification1 c1
FULL JOIN tblCertification2 c2
ON c2.EmployeeID = c1.EmployeeID
AND c2.VisitDate = c1.VisitDate
FULL JOIN tblCertification3 c3
ON c3.EmployeeID = COALESCE (c1.EmployeeID, c2.EmployeeID)
AND c3.VisitDate = COALESCE (c1.VisitDate, c2.VisitDate))
AS f
ON e.EmployeeID = f.EmployeeID

The above is untested, since you didn't follow Erland's advise to post
CREATE TABLE and INSERT statements and desired output.

Now, triple full joins are definitely the simplest one to write. I
had to do this in real life a year back. I arrived at:

SELECT coaleace(ab.col1, c.col1), ...
FROM (SELECT col1 = coleasce(a.col1, b.col1), ...
FROM a
FULL JOIN b ON a.col1 = b.col1) AS ab
FULL JOIN c ON c.col1 = ab.col1

The obvious problem with the above is that the coalesce is very
likely to prohibit the use of indexes. Still I have a feeling
that I overdid it, but I have not found anything better.


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp