Databases Reference
In-Depth Information
End Sub
19.9 A Matching Problem
Table 19-17 presents programmers and their language skills. Table 19-18 specifies the
language requirements for a number of different jobs. We want to display a list of the
jobs and their respective qualified programmers.
Table 19-17. Programmers table: Prog
r
ammers and their language skills
Name
Language
B
laise Pascal
V
B
B
laise Pascal
C
++
B
laise Pascal
A
ccess
B
laise Pascal
E
xcel
G
auss
V
B
G
auss
A
ccess
G
auss
D
elphi
G
auss
S
QL Server
S
mith
C
++
V
on Neuman
V
B
V
on Neuman
C
++
Wordsworth
D
elphi
Wordsworth
C
++
Wordsworth
Word
Table 19
-
18. ProgrammingJobs table
JobID
Language
1
V
B
1
A
ccess
2
C++
3
C
++
3
S
QL Server
4
D
elphi
5
V
B
5
P
ascal
19.9.1 Solution
One solution is given by the following SQL statement:
SELECT ProgrammingJobs.JobID, Programmers.Name
FROM Programmers INNER JOIN ProgrammingJobs
ON Programmers.Language = ProgrammingJobs.Language
GROUP BY ProgrammingJobs.JobID, Programmers.Name
HAVING Count(Programmers.Language)=
(SELECT Count([Language]) FROM ProgrammingJobs AS PJ
WHERE PJ.JobID=ProgrammingJobs.JobID)
