CODEWITHSUNDEEP
mysqlsqldatabase
Garry Pettet
Garry Pettet

Reputation: 8290

How should I construct this MySQL query?

I have a database with four tables:

users
- id
- name

sessions
- id
- title

sessions_users
- id
- sessions_id
- users_id

session_feedback
- id
- sessions_id
- users_id

Users are set to attend certain sessions (by adding their id to the sessions_users table). Users are also expected to leave feedback on a session. When done, the id of the session (sessions_id) and the id of the user leaving the feedback (users_id) is entered into the session_feedback table.

Given a particular user's id, I can't figure out how to select the sessions that have not had feedback yet (i.e. there is no entry in session_feedback containing both the sessions_id and the users_id). I.e. I need to select all sessions that have feedback pending.

Can anyone help at all?

Upvotes: 0

Views: 59

Answers (2)

classicjonesynz
classicjonesynz

Reputation: 4042

You can do it with a LEFT join.

SELECT s.* FROM sessions s
LEFT JOIN session_feedback sf ON sf.sessions_id = s.id
WHERE sf.session_id IS NULL

The query simply retrieves all records from the sessions table where there is no link to the feedback table (thus giving you sessions without feedback). If you want to include sessions that have users where there is no feedback.

SELECT s.* FROM sessions s
INNER JOIN sessions_users su ON su.sessions_id = su.id
INNER JOIN users u ON su.user_id = u.id
LEFT JOIN session_feedback sf ON sf.users_id = u.id
WHERE sf.session_id IS NULL

To further improve or tighten the query, just add to the WHERE clause.

Upvotes: 1

wribit
wribit

Reputation: 605

Is this what you wanted? This will return all sessions ids and titles that have not had feedback yet. 

    select sessions.id, sessions.title
    from sessions where sessions.id NOT IN 
        (select sessions_id from session_feedback)

And for a specific user:

    SELECT sessions.id, sessions.title
    FROM sessions INNER JOIN sessions_user on
        sessions.id = sessions_user.sessions_id
    WHERE sessions.id NOT IN (select sessions_id from session_feedback)
    AND sessions_user.users_id = [given user id]

Upvotes: 2

Related Questions