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. tblCertificatio n1
a. VisitDate
b. CertificationSc ore1

3. tblCertificatio n2
a. VisitDate
b. CertificationSc ore2

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

EmployeeID | VisitDate | CertificationSc ore1 | CertificationSc ore2
1 | 02/02/05 | 50 | 50

If they didn't take Certification2 then leave it NULL

EmployeeID | VisitDate | CertificationSc ore1 | CertificationSc ore2
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.c om

[David Rawheiser]

<cw*****@gmail. com> wrote in message
news:11******** **************@ c13g2000cwb.goo glegroups.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. tblCertificatio n1
a. VisitDate
b. CertificationSc ore1

3. tblCertificatio n2
a. VisitDate
b. CertificationSc ore2

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

EmployeeID | VisitDate | CertificationSc ore1 | CertificationSc ore2
1 | 02/02/05 | 50 | 50

If they didn't take Certification2 then leave it NULL

EmployeeID | VisitDate | CertificationSc ore1 | CertificationSc ore2
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.c om

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. tblCertificatio n1
a. EmployeeID
b. VisitDate
c. CertificationSc ore1

3. tblCertificatio n2
a. EmployeeID
b. VisitDate
c. CertificationSc ore2

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.c om

[--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 CertificationTe sts
(emp_id CHAR(9) NOT NULL
REFERENCES Personnel(emp_i d)
ON UPDATE CASCADE
ON DELETE CASCADE,
test_date DATETIME DEFAULT CURRENT_TIMESTA MP 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. tblCertificatio n1
a. EmployeeID
b. VisitDate
c. CertificationSc ore1

3. tblCertificatio n2
a. EmployeeID
b. VisitDate
c. CertificationSc ore2

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.Emp loyeeID, c2.EmployeeID),
VisitDate = coalesce(c1.Vis itDate, c2.VisitDate),
Score1 = c1.Certificacti onScore1,
Score2 = c1.Certificacti onScore2
FROM tblCertificatio n1 c1
FULL JOIN tblCertificatio n2 c2
ON c1.EmployeeID = c2.EmployeeID
AND c1.VisitDate = c2.VisitDate) AS f
ON e.EmployeeID = f.EmployeeID

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.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 CertificationSc ore3 ?

Thanks,

cwwilly

[Hugo Kornelis]

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

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

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
(CertificationN o int NOT NULL
,EmployeeID int NOT NULL
,VisitDate smalldatetime NOT NULL
,CertificationS core smallint
,PRIMARY KEY (CertificationN o, EmployeeID, VisitDate)
,FOREIGN KEY (EmployeeID) REFERENCES Employees
,CHECK (CertificationN o 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.Emp loyeeID, c2.EmployeeID,
c3.EmployeeID),
VisitDate = coalesce(c1.Vis itDate, c2.VisitDate,
c3.VisitDate),
Score1 = c1.Certificacti onScore1,
Score2 = c2.Certificacti onScore2,
Score3 = c3.Certificacti onScore3
FROM tblCertificatio n1 c1
FULL JOIN tblCertificatio n2 c2
ON c2.EmployeeID = c1.EmployeeID
AND c2.VisitDate = c1.VisitDate
FULL JOIN tblCertificatio n3 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_rFa ct.in_SPAM_fo) writes:

SELECT e.EmployeeID, f.VisitDate, f.Score1, f.Score2, f.Score3
FROM tblEmployee e
JOIN (SELECT EmployeeID = coalesce(c1.Emp loyeeID, c2.EmployeeID,
c3.EmployeeID),
VisitDate = coalesce(c1.Vis itDate, c2.VisitDate,
c3.VisitDate),
Score1 = c1.Certificacti onScore1,
Score2 = c2.Certificacti onScore2,
Score3 = c3.Certificacti onScore3
FROM tblCertificatio n1 c1
FULL JOIN tblCertificatio n2 c2
ON c2.EmployeeID = c1.EmployeeID
AND c2.VisitDate = c1.VisitDate
FULL JOIN tblCertificatio n3 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.col 1, 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****@sommarsk og.se

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