I have the following tables:
Applicant ApID Name 1 Jones 2 Parker 3 Smith Preferred Grade ApID Grade 1 7 2 12 3 7 Preferred Subject ApID Subject 1 math 2 English 3 math Position ApID DesiredPosition 1 Teacher-Grades 7-8 2 Teacher-Grades 7-8 3 Teacher-Grades 7-8 License ApID Type 1 Professional-Standard 2 Probationary 3 Professional-StandardI want to List all Applicant Names that meet the following criterion:
License type of ' Professional-Standard' Desired Position of ' Teacher-Grades 1-2' Preferred Subject of 'math' Preferred Grade of '7'Correct result would be Jones and Smith, but I am unsuccessful in accomplishing this with joins. I do get the correct result with a Union, but I am using Microsoft Access and am required to use a Select rather than a Union to requery an applicant form.
This is a simple star schema with the Applicant table as the center of the configuration. Here's the SQL:
select Name from Applicant a, PreferredGrade pg, PreferredSubject ps, Position p, License l where a.ApID = pg.ApID and a.ApID = ps.ApID and a.ApID = p.ApID and a.ApID = l.ApID and pg.Grade = 7 and ps.Subject = 'math' and p.DesiredPosition = 'Teacher-Grades 7-8' and l.Type = 'Professional-Standard';And here are the results:
NAME ----- Jones Smith
For More Information
- What do you think about this answer? E-mail the edtiors at editor@searchDatabase.com with your feedback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.