Query multiple tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Query multiple tables

Author

Topic

noblemfd
Starting Member

38 Posts

Posted - 2014-09-11 : 02:28:03

i have three tables
1. tblstaff: fields=> staffid(primary key),staffname
2. tblinitiator: fields=> initiatorid(primary key), fk_staffid(foreign key), fk_appraisalid(foreign key)
3. tblappraisal: fields=> appraisalid(primary key), appraisal_date, score.
NOTE: Some of the staff members are also appraisers. they appraise other staff. An appraisee is a staff appraised by other staff while appraiser appraises the appraisee.

I want to query the table to have these result.

appraisalid | appraisal_date | appraisee | appraiser | score

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-11 : 09:55:31

there seems to be something missing. tblappraisal does not indicate who is being appraised. put another way, if you are looking at the data, how can you tell who is an appraiser and who is an appraisee? Post some sample data and show the relationships

noblemfd
Starting Member

38 Posts

Posted - 2014-09-12 : 06:31:59

quote:
Originally posted by gbritton

there seems to be something missing. tblappraisal does not indicate who is being appraised. put another way, if you are looking at the data, how can you tell who is an appraiser and who is an appraisee? Post some sample data and show the relationships

Am sorry, I didn't give you adequate information. There was also a slight mistake. see the detail one:

1. tblemployee: fields=> id(primary key),last_name, first_name, middle_name
2. tblappraisal: fields=> id(primary key), date_start,date_end, date_review, employeeid(foreign key)
3. tblunit: fields=> unitid(primary key), unit_name, head_id(unique, but not foreign key).
4. tblscorecard: field=> scorecard_id(primary key), status,score,employee_id.

All the staff in tblemployee table belong to a unit. each unit has a head or supervisor with head_id in the tblunit table. the field head_id is unique(there is only one unit head) but not a primary key.
The appraisal will be initiated by a staff member with foreign key employee_id in the tblappraisal table. He will also be appraised, but he will also be appraised.

So all the employee will be appraised apart from the supervisor.
The appraisal will be done by the supervisor (head of unit).
The appraisal is initiated by the initiator, with employee_id int tblappraisal table.

appraisee are staff to be appraised.

CONCATENATE
supervisor = last_name + first_name _ middle_name
initiator = last_name + first_name _ middle_name
appraisee = last_name + first_name _ middle_name

See the script below:

CREATE TABLE IF NOT EXISTS `tblappraisal` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`date_start` date DEFAULT NULL,
`date_end` date DEFAULT NULL,
`date_review` date DEFAULT NULL,
`purpose` varchar(50) DEFAULT NULL,
`employee_id` int(6) DEFAULT NULL,
PRIMARY KEY (`id`),
FOREIGN KEY (`employee_id`) REFERENCES tblemployee(id)
) ;

INSERT INTO `tblappraisal` (`id`, `date_start`, `date_end`, `date_review`, `purpose`, `employee_id`) VALUES
(1, '2014-05-13', '2014-05-23', '2014-05-29', 'promotion', 000004),
(2, '2014-06-13', '2014-06-24', '2014-06-30', 'promotion', 000004),
(3, '2014-07-13', '2014-07-27', '2014-07-28', 'training', 000004),
(4, '2014-08-13', '2014-08-23', '2014-08-30', 'training', 000005),
(5, '2014-09-01', '2014-09-11', '2014-09-11', 'promotion', 000008);

CREATE TABLE IF NOT EXISTS `tblemployee` (
`id` int(6) NOT NULL AUTO_INCREMENT,
`last_name` varchar(50) NOT NULL,
`first_name` varchar(50) NOT NULL,
`middle_name` varchar(50) DEFAULT NULL,
`unit_id` int(4) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unit_id` (`unit_id`)
);

INSERT INTO `tblemployee` (`id`, `last_name`, `first_name`, `middle_name`, `unit_id`) VALUES
(000001, 'Jason', 'Martin', 'Sylvia', 0001),
(000002, 'Alison', 'Mathews', 'Keagan', 0002),
(000003, 'James', 'Smith', 'Oslo', 0003),
(000004, 'Celia', 'Rice', '', 0001),
(000005, 'Robert', 'Black', 'Jacob', 0002),
(000006, 'Davou', 'Pam', 'Choji', 0001),
(000007, 'Olabode', 'Thomas', 'K', 0002),
(000008, 'Howard', 'Smith', 'Kolan', 0003),
(000009, 'Craig', 'Yinka', '', 0001),
(000010, 'Wain', 'Leo', 'Jacob', 0003);

CREATE TABLE IF NOT EXISTS `tblscorecard` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`status` varchar(30) DEFAULT NULL,
`score` decimal(10,2) DEFAULT NULL,
`employee_id` int(6) DEFAULT NULL,
PRIMARY KEY (`id`),
FOREIGN KEY (`employee_id`) REFERENCES tblemployee(id)
) ;

INSERT INTO `tblscorecard` (`id`, `status`, `score`, 'employee_id`) VALUES
(1, 'planned', 6,000004),
(2, 'transfered', 7, 000005),
(3, 'planned', 4, 000006),
(4, 'planned', 5, 000007),
(5, 'transfered', 8, 000008);

CREATE TABLE IF NOT EXISTS `tblunit` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`unit_name` varchar(50) NOT NULL,
`head_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY(`head_id`)
);

INSERT INTO `tblunit` (`id`, `unit_name`, `head_id`) VALUES
(0001, 'account', 1),
(0002, 'research', 2),
(0003, 'marketing', NULL);

FINAL RESULT
S/N| Supervisor_id|Supervisor|initiator_id|initiator|appraisee_id|appraisee|appraisal_id|date_start|date_end|date_review|status|score|

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources