-1

I'm a graduating student and I got this tables to for my School Thesis.

I'm using PHP, and MYSQL for this one.

**My Tables so Far .. **

Event Table

id title
1 Charity Work
2 Cleaning Day

User Table

id first_name
16 John
17 Mark
18 Doe

Registration Table

Reg_ID Event_ID User_ID
1 1 16
2 2 17

My problem is I cannot figure out how to show the events that users didn't register as result.

I already figured out how to show the events user registered but no luck to show the opposite.

My Code for Showing events they register:

SELECT events.id, Event_ID,events.title, USER_ID,users.first_name from events INNER JOIN event_registration ON events.id = Event_ID INNER JOIN users ON users.id = USER_ID WHERE DATE_FORMAT(start,'%Y-%m-%d') >= CURRENT_DATE AND $user = USER_ID ORDER BY start;

Expected Result should be

user_id event_id
18 1
18 2
16 2
17 1

EDIT ---- Attempts made:

SELECT events.id, Event_ID,events.title, USER_ID from events left JOIN event_registration ON events.id = Event_ID WHERE USER_ID is null;

Result:

Will only show results that are null and not events users haven't registered yet.

Does someone know or experience similar to this?

Thank you!

Fitzgerald Ang
  • 1
  • 2
  • 1
    What attempt have you made? Where are you stuck? – ADyson Dec 22 '22 at 14:00
  • Honestly my attempts was just trying to do the opposite way of showing list of events someone registered but none of them were showing a result and yes, i just want a list of events that no-one has registered. – Fitzgerald Ang Dec 22 '22 at 14:03

1 Answers1

1

A left join should do it:

SELECT events.id, Event_ID,events.title, USER_ID,users.first_name 
from events left JOIN event_registration ON events.id = Event_ID 
WHERE event_registration is null
Jens
  • 67,715
  • 15
  • 98
  • 113