vendredi 24 juin 2016
Is it possible to build this query in SQL or With PL/SQL?
I have This is Tables in Oracle Database:
1- Users Table
╔════════╦═════════╗
║ UserID ║ Name ║
╠════════╬═════════╣
║ 12345 ║ Scott ║
╠════════╬═════════╣
║ 54321 ║ Jeffry ║
╠════════╬═════════╣
║ 98765 ║ Larry ║
╚════════╩═════════╝
2- ScoreTypes Table
╔══════════════════╦════════════╗
║ ScoreTypeID (PK) ║ Title ║
╠══════════════════╬════════════╣
║ 1 ║ Football ║
╠══════════════════╬════════════╣
║ 2 ║ Volleyball ║
╠══════════════════╬════════════╣
║ 3 ║ Basketball ║
╚══════════════════╩════════════╝
3- UserResults
╔═════════════╦══════════════════╦══════════╗
║ UserID (FK) ║ ScoreTypeID (FK) ║ ScoreNum ║
╠═════════════╬══════════════════╬══════════╣
║ 12345 ║ 1 ║ 90 ║
╠═════════════╬══════════════════╬══════════╣
║ 12345 ║ 2 ║ 45 ║
╠═════════════╬══════════════════╬══════════╣
║ 54321 ║ 1 ║ 95 ║
╠═════════════╬══════════════════╬══════════╣
║ 54321 ║ 2 ║ 60 ║
╠═════════════╬══════════════════╬══════════╣
║ 98765 ║ 1 ║ 100 ║
╠═════════════╬══════════════════╬══════════╣
║ 98765 ║ 3 ║ 35 ║
╚═════════════╩══════════════════╩══════════╝
4- ScorePolicyPasses Table
╔═════════╦══════════════════╦═══════════╗
║ ID (PK) ║ ScoreTypeID (FK) ║ PassScore ║
╠═════════╬══════════════════╬═══════════╣
║ 1 ║ 1 ║ 89 ║
╠═════════╬══════════════════╬═══════════╣
║ 2 ║ 2 ║ 50 ║
╚═════════╩══════════════════╩═══════════╝
What idea?
There may be records for User in Table "UserResults"
It contains a column to display score Number obtained in the specific game in table "ScoreTypes".
Each game has score pass specific to Win in Table "ScorePolicyPasses"
I want to write a single query
To return the users who have the records in "UserResults Table"
but, Only those who did not go beyond the policies in "ScorePolicyPasses Table"
Result SQL Query:
╔══════════════╦════════════╦══════════════════════════════╗
║ Users.UserID ║ Users.Name ║ Comment ║
╠══════════════╬════════════╬══════════════════════════════╣
║ 12345 ║ Scott ║ Football: 90, Volleyball: 45 ║
╚══════════════╩════════════╩══════════════════════════════╝
Only one user did not achieve the results that have been previously put her policies
Soctt has score in Football 90 Good but in Volleyball 45 < 50
too, If it were football < 90 and Volleyball < 50 did not achieve
Jeffry Achieved the desired football > 90 and Volleyball > 50
Larry Achieved the desired football > 90
Although Larry has record of basketball in Table "UserResults"
But basketball has no policy in "ScorePolicyPasses Table",
Therefore, the recored will be ignored.
Is it possible to write a query by SQL Only Or with PLSQL? And how?
Inscription à :
Publier les commentaires (Atom)
Aucun commentaire:
Enregistrer un commentaire