I like to set the priority on rows returned based on values

Pakeeza

I have two tables one is students_Games and one is students tables both have a student_id a primary foriegn key relationship now based on their games I am putting them in some queue

Student table {Student_id,name,Address}

Students_games(id,Student_ID(FOREIGNKEY,game_code),

table_ID	student_id	game Code
1	1	200
2	2	200
3	2	300
4	3	200
5	3	300
6	4	870

My Qyuery is like this

Select * from studnets ss

join students_games gg on gg.Student_id=ss.student_Id where gg.Games_code in (200,300)

Now when i run this for student id 2 and 3 it will return two,two rows one for 200 and one for 300 , now I want to set the prioirty like if there is 200 available then it must return 200, if not then it should return 300 if both of them not present then it should return 0 . how to query it like that i mean if 200 game code is available then chose it for every student and don't chose 300 for it if 200 is available if 200 is not available then chose 300 as game code if both of them not there then chose 0

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Mohanty2y ago

Accepted answer

Try this:

SELECT ss.Student_id,
       ss.Name,
       ss.Address,
       COALESCE(MAX(CASE WHEN gg.Game_code = 200 THEN gg.Game_code END), 
                MAX(CASE WHEN gg.Game_code = 300 THEN gg.Game_code END), 
                0) AS Selected_Game
FROM students ss
LEFT JOIN students_games gg ON gg.Student_id = ss.Student_id AND gg.Game_code IN (200, 300)
GROUP BY ss.Student_id, ss.Name, ss.Address
ORDER BY ss.Student_id;

Check the example here: https://dbfiddle.uk/KapfXUSZ

Pakeeza2y ago
Dear Amit thanks for the help , i checked your query without group by it works fine for the case when we have 200,300 and in case if we have not either of them then it return 0 , but there is any issue like when we have only 200 then it return nothing and when we have only 300 then it returns nothing , if it has both then it return min that is fine.

Amit Mohanty2y ago

If you don't want to use Group By then try this:

SELECT 
    ss.Student_id,
    ss.Name,
    ss.Address,
    COALESCE((
        SELECT MIN(gg.Game_code)
        FROM students_games gg
        WHERE gg.Student_id = ss.Student_id
        AND gg.Game_code IN (200, 300)
    ), 0) AS Selected_Game
FROM students ss
ORDER BY ss.Student_id;

Pakeeza2y ago
hi Amit thanks for the reply but there is an issue I cannot use group by due to few reasons as it disturb the query is there any other way around as my games_code table is seperate table so there might be other options I canot use min,max so these require group by and I do not want to group by my records
Pakeeza2y ago
Thanks Amit for helping me out.