Question 113654 in Mysql Question posted in
The official MySQL documentation can be found here.

MYSQL – Having clause does not return any rows

Student Visa Approval Process

Table: STUDENT_MST

VISA_APPROVED:
    'P': Visa Approval is Pending
    'Y': Visa is Approved
    'N': Visa is not Approved

UNIVERSITY_APPROVED:
    'P': University Approval is Pending
    'Y': University has approved the Student
    'N': University has rejected the Student
STUDENT_MST_ID STUDENT_ID STUDENT_FILE_ID VISA_APPROVED UNIVERSITY_APPROVED
1 ‘STU-1’ ‘FILE-1’ ‘P’ ‘P’
2 ‘STU-2’ ‘FILE-2’ ‘Y’ ‘Y’
3 ‘STU-3’ ‘FILE-3’ ‘N’ ‘N’
4 ‘STU-3’ ‘FILE-4’ ‘Y’ ‘Y’
5 ‘STU-4’ ‘FILE-5’ ‘N’ ‘Y’
6 ‘STU-4’ ‘FILE-6’ ‘Y’ ‘Y’
7 ‘STU-5’ ‘FILE-7’ ‘N’ ‘Y’
8 ‘STU-5’ ‘FILE-8’ ‘N’ ‘Y’
9 ‘STU-5’ ‘FILE-9’ ‘Y’ ‘Y’ 
Table: FILE_MST

FILE_PROCESSED:
    'N': File not processed
    'Y': File Processed
FILE_MST_ID STUDENT_FILE_ID FILE_PROCESSED
1 ‘FILE-1’ ‘N’
2 ‘FILE-2’ ‘Y’
3 ‘FILE-3’ ‘N’
4 ‘FILE-4’ ‘Y’
5 ‘FILE-5’ ‘Y’
6 ‘FILE-6’ ‘Y’
7 ‘FILE-7’ ‘Y’
8 ‘FILE-8’ ‘Y’
9 ‘FILE-9’ ‘Y’

I want to fetch all the records where a student has applied more than once [COUNT(studntMst.STUDENT_ID) > 1] and only their Visa got first rejected and then approved [studntMst.VISA_APPROVED in (‘N’,’Y’)] and the file is processed [fileMst.FILE_PROCESSED = ‘Y’]

Following query is not returning any output.

SELECT 
studntMst.STUDENT_MST_ID,
studntMst.STUDENT_ID,
studntMst.VISA_APPROVED,
studntMst.UNIVERSITY_APPROVED,
fileMst.STUDENT_FILE_ID,
fileMst.FILE_PROCESSED
FROM 
STUDENT_MST studntMst
JOIN FILE_MST fileMst on fileMst.STUDENT_FILE_ID = studntMst.STUDENT_FILE_ID
and studntMst.VISA_APPROVED in ('N','Y')
and fileMst.FILE_PROCESSED = 'Y'
GROUP BY
studntMst.STUDENT_MST_ID,
studntMst.STUDENT_ID,
studntMst.VISA_APPROVED,
studntMst.UNIVERSITY_APPROVED,
fileMst.STUDENT_FILE_ID,
fileMst.FILE_PROCESSED
HAVING COUNT(studntMst.STUDENT_ID) > 1
ORDER BY studntMst.STUDENT_MST_ID desc;

Ideally it should return the following.

STUDENT_MST_ID STUDENT_ID VISA_APPROVED UNIVERSITY_APPROVED STUDENT_FILE_ID FILE_PROCESSED
9 STU-5 ‘Y’ ‘Y’ FILE-8 ‘Y’
8 STU-5 ‘N’ ‘Y’ FILE-7 ‘Y’
7 STU-5 ‘N’ ‘Y’ FILE-6 ‘Y’
6 STU-4 ‘Y’ ‘Y’ FILE-5 ‘Y’
5 STU-4 ‘N’ ‘Y’ FILE-4 ‘Y’

Queries to create table and insert data.

Tags:

2

Answers


  1. 0 
    SELECT 
*
FROM 
STUDENT_MST studntMst
JOIN FILE_MST fileMst on fileMst.STUDENT_FILE_ID = studntMst.STUDENT_FILE_ID
and studntMst.STUDENT_ID in (
    SELECT 
    studntMst.STUDENT_ID
    FROM 
    STUDENT_MST studntMst
    JOIN FILE_MST fileMst on fileMst.STUDENT_FILE_ID = studntMst.STUDENT_FILE_ID
    and studntMst.VISA_APPROVED in ('N','Y')
    and fileMst.FILE_PROCESSED = 'Y'
    GROUP BY
    studntMst.STUDENT_ID
    HAVING COUNT(studntMst.STUDENT_ID) > 1
)
ORDER BY studntMst.STUDENT_MST_ID desc;
    Login or Signup to reply.
  2. 0

    You can follow up with three steps:

    • join the "STUDENT_MST" table with the "FILE_MST" table on matching "STUDENT_FILE_ID" field and filtering out non-processed files (f.FILE_PROCESSED <> 'Y')
    • use two SUM window functions, one to identify the amount of rejected visas, another to identify the amount of approved visas
    • filter out rows that have these window function values less than 1 (which means that the student has had their visa accepted and rejected at least once)
    WITH cte AS (
    SELECT s.*, 
           SUM(CASE WHEN VISA_APPROVED = 'Y' THEN 1 END) OVER(PARTITION BY STUDENT_ID) AS y,
           SUM(CASE WHEN VISA_APPROVED = 'N' THEN 1 END) OVER(PARTITION BY STUDENT_ID) AS n
    FROM       STUDENT_MST s
    INNER JOIN FILE_MST    f
            ON s.STUDENT_FILE_ID = f.STUDENT_FILE_ID
           AND f.FILE_PROCESSED = 'Y'
)
SELECT STUDENT_MST_ID,
       STUDENT_ID,
       STUDENT_FILE_ID,
       VISA_APPROVED,
       UNIVERSITY_APPROVED
FROM cte
WHERE y >= 1 AND n >= 1

    Check the demo here.

    Assumption: rejected visas come always before accepted visas. If this is not the case, you need an additional LAST_VALUE window function which gathers the last value for the visa state, partitioned by each student. Eventually filter out those students whose last visa state is not ‘Y’.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search