CODEWITHSUNDEEP
sqlmysql
Newbie
Newbie

Reputation: 1714

Need help building SQL Query (simple JOIN)

In my database, I have a "users", a "quests" and a "questings" table. A user can solve a quest. Solving a quest will save the "user_id" and the "quest_id" in my "questings" table.

Now, I want to select all quests, a user has NOT solved (meaning there is no entry for this user and quest in "questings" table)!

Let's say the user has the id 14. How to write this query?

After solving this query, I want to filter the results, too. A quest and a user has a city, too. What to do for writing a query which returns all quests, a user has NOT solved yet, in the users city (user city == quest city)?

Upvotes: 0

Views: 109

Answers (2)

Salil
Salil

Reputation: 47472

select * from quests q, users u
  where u.id = 14 AND q.city=u.city AND 
          q.id not in ( select DISTINCT(quest_id) from questing );

Upvotes: 2

Neil Knight
Neil Knight

Reputation: 48537

SELECT *
  FROM Quests
 WHERE Quest_ID NOT IN (
       SELECT DISTINCT(Quest_ID)
         FROM Questing
        WHERE User_ID = 14)

Upvotes: 2

Related Questions