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?

Aucun commentaire:

Enregistrer un commentaire