473,327 Members | 2,094 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

SQL Query Help

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

Jul 23 '05 #1
9 2700

<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

Jul 23 '05 #2
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

Jul 23 '05 #3
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));

Jul 23 '05 #4
(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
Jul 23 '05 #5
Thank you so much ! You guys rock. I got it figured out.

Jul 23 '05 #6
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

Jul 23 '05 #7
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)
Jul 23 '05 #8
Thanks Hugo ! It worked like a charm. I really appreciate you helping
me with this.

cwwilly

Jul 23 '05 #9
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
Jul 23 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: netpurpose | last post by:
I need to extract data from this table to find the lowest prices of each product as of today. The product will be listed/grouped by the name only, discarding the product code - I use...
7
by: Simon Bailey | last post by:
How do you created a query in VB? I have a button on a form that signifies a certain computer in a computer suite. On clicking on this button i would like to create a query searching for all...
4
by: d.p. | last post by:
Hi all, I'm using MS Access 2003. Bare with me on this description....here's the situation: Imagine insurance, and working out premiums for different insured properties. The rates for calculating...
4
by: Alan Lane | last post by:
Hello world: I'm including both code and examples of query output. I appologize if that makes this message longer than it should be. Anyway, I need to change the query below into a pivot table...
36
by: Liam.M | last post by:
hey guys, I have one last problem to fix, and then my database is essentially done...I would therefore very much appreciate any assistance anyone would be able to provide me with. Currently I...
5
by: elitecodex | last post by:
Hey everyone. I have this query select * from `TableName` where `SomeIDField` 0 I can open a mysql command prompt and execute this command with no issues. However, Im trying to issue the...
10
by: aaronrm | last post by:
I have a real simple cross-tab query that I am trying to sum on as the action but I am getting the "data type mismatch criteria expression" error. About three queries up the food chain from this...
11
by: funky | last post by:
hello, I've got a big problem ad i'm not able to resolve it. We have a server running oracle 10g version 10.1.0. We usually use access as front end and connect database tables for data extraction....
4
by: Doris | last post by:
It does not look like my message is posting....if this is a 2nd or 3rd message, please forgive me as I really don't know how this site works. I want to apologize ahead of time for being a novice...
6
by: jsacrey | last post by:
Hey everybody, got a secnario for ya that I need a bit of help with. Access 97 using linked tables from an SQL Server 2000 machine. I've created a simple query using two tables joined by one...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.