CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11g
Ricky Suhendar
Ricky Suhendar

Reputation: 125

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                ║ 89       ║
╠═════════════╬══════════════════╬══════════╣
║ 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                ║ 90        ║
╠═════════╬══════════════════╬═══════════╣
║ 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: 89, 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?

Upvotes: 2

Views: 104

Answers (1)

MT0
MT0

Reputation: 168026

Oracle Setup:

CREATE TABLE Users ( UserID, Name ) AS
SELECT 12345, 'Scott'  FROM DUAL UNION ALL
SELECT 54321, 'Jeffry' FROM DUAL UNION ALL
SELECT 98765, 'Larry'  FROM DUAL;

CREATE TABLE ScoreTypes ( ScoreTypeID, Title ) AS
SELECT 1, 'Football' FROM DUAL UNION ALL
SELECT 2, 'Volleyball' FROM DUAL UNION ALL
SELECT 3, 'Basketball' FROM DUAL;

CREATE TABLE UserResults ( UserID, ScoreTypeID, ScoreNum ) AS
SELECT 12345, 1, 89 FROM DUAL UNION ALL
SELECT 12345, 2, 45 FROM DUAL UNION ALL
SELECT 54321, 1, 95 FROM DUAL UNION ALL
SELECT 54321, 2, 60 FROM DUAL UNION ALL
SELECT 98765, 1, 100 FROM DUAL UNION ALL
SELECT 98765, 3, 35 FROM DUAL;

CREATE TABLE ScorePolicyPasses ( ID, ScoreTypeID, PassScore ) AS
SELECT 1, 1, 90 FROM DUAL UNION ALL
SELECT 2, 2, 50 FROM DUAL;

Query:

SELECT ur.UserID   AS "Users.UserID",
       MAX( Name ) AS "Users.Name",
       LISTAGG( Title || ': ' || ScoreNum, ', ' )
         WITHIN GROUP ( ORDER BY ur.ScoreTypeID ) AS "Comment"
FROM   UserResults ur
       INNER JOIN Users u
       ON ( ur.UserId = u.UserID )
       INNER JOIN ScoreTypes s
       ON ( ur.ScoreTypeID = s.ScoreTypeID )
       INNER JOIN ScorePolicyPasses p
       ON ( ur.ScoreTypeID = p.ScoreTypeID )
GROUP BY ur.UserID
HAVING COUNT( CASE WHEN ScoreNum < PassScore THEN 1 END ) > 0;

Output:

Users.UserID Users.Name Comment
------------ ---------- ----------------------------------------------------
       12345 Scott      Football: 89, Volleyball: 45

Upvotes: 5

Related Questions