Question 161760 in PostgreSQL Question posted in
The official documentation can be found here.

How do I write this PostgreSQL query?

I am trying to write a SQL query that selects the first (based off smallest ID) quest an NPC has available.

If the user already has a quest from that NPC do not return that quest.

Quests table

id | npc     | name
---+---------+------------
 1 | NPC-1   | Kill an Ogre 
 2 | NPC-1   | Kill a Bear
 3 | NPC-2   | Kill a Cow

quest_completions table : if there’s an entry in here it means the quest is in progress unless rewarded is set to true then it is fully done and rewarded and the next quest is available.

quest_name   |  rewarded   |  player_id
-------------+-------------+-----------
Kill an Ogre |   false     |      0

I want to return [NPC-2] because that is the only NPC with an available quest.

I can show you my SQL query but for some reason it isn’t working. I’m not really sure how to debug it.

SELECT 
    npc
FROM 
    (SELECT DISTINCT 
         ON(npc) quests.npc,
         MIN(quests.id),
         quests.name
     FROM 
         quests
     LEFT JOIN 
         quest_completions ON quest_completions.quest_name = quests.name
      WHERE 
          (quest_completions.rewarded IS NULL
           OR quest_completions.rewarded = false)
          AND quest_completions.player_id = 5
      GROUP BY 
          npc, quests.name) a
WHERE 
    a.name NOT IN (SELECT quest_completions.quest_name
                   FROM quest_completions
                   LEFT JOIN quests ON quest_completions.quest_name = quests.name
                   WHERE rewarded = false);

Edit: I actually created a very detailed fiddle that includes test data and expected output https://www.db-fiddle.com/f/iNs4C1FCitLQqeDzMJg1xn/3

Tags:

2

Answers


  1. 0 
    select distinct q.npc from quests q
where q.name not in (
select q.name from quests q
left join quest_completions qc on q.name = qc.quest_name and qc.player_id=0
where qc.rewarded=true);

    I work with your fiddle. I think it’s working… Take a try.

    Here is fiddle:
    https://www.db-fiddle.com/f/iNs4C1FCitLQqeDzMJg1xn/7

    Login or Signup to reply.
  2. 0

    Ok, after many tests I finally came up with a working solution.

    Here is the query:

    select npc, min(id) questId
  from quests q 
 where q.id not in (select q.id
               from quests q
              inner join quest_completions qc on q.name = qc.quest_name
              where player_id = 0
            )
  and q.npc not in (select q.npc
               from quests q
              inner join quest_completions qc on q.name = qc.quest_name
             where (qc.rewarded = false or qc.rewarded is null)
               and player_id = 0
            )
group by npc;

    I had to think of excluding all in progress quest quests per NPC then any quest per player

    Here is the fiddle: https://www.db-fiddle.com/f/ddf7ZrFhdn9HpKWY4rdXs1/0

    I’ve tested all sorts of combinations in my local environment.

    Like:
    case 1: Returns NPC-2, quest id 3

    delete from quest_completions
insert into quest_completions values ('Kill an Ogre', false, 0);

    case 2: Returns NPC-2, quest id 3

    delete from quest_completions
insert into quest_completions values ('Kill a Bear', false, 0);

    case 3: Returns NPC-1, quest id 1

    delete from quest_completions
insert into quest_completions values ('Kill a Cow', false, 0);

    case 4: Returns NPC-1/quest id 2 and NPC-2/quest id 3

    delete from quest_completions
insert into quest_completions values ('Kill an Ogre', true, 0);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search