CODEWITHSUNDEEP
mysqljoinreplace
Naterade
Naterade

Reputation: 2675

Mysql -- how to join 2 tables and only keep matching row if criteria is met

I have a recurring event calendar that allows people to sign up for events. I have 2 tables dynamic_events and recurring_events in the following structure:

dynamic events
id (int)
date (date)
time (time)
title (varchar[255])
desc (text)
max_attendees (int)
is_overwrite (tinyint[1])

recurring events
id (int)
day_of_week (int)
time (time)
title (varchar[255])
desc (text)

Whenever a recurring event is updated, I make it dynamic to uniquely identify it against the attendee table. The way it works now is I query for all the dynamic events for the day then I query for all the recurring events for the day and pass the arrays to a PHP function to replace any recurring event with its dynamic event if the date and time match and is_overwrite is equal to 1. Basically meaning that the recurring event for that date and time has been updated to allow folks to sign up. This works but I was wondering if this would be possible without PHP with a left join of some kind. Replacing any row from the recurring table that has a dynamic counterpart to overwrite it while keeping all other events? I'm just not sure how to go about it and was looking for a step in the right direction. Hopefully I'm not too confusing in my explantion.

Upvotes: 1

Views: 174

Answers (2)

Bohemian
Bohemian

Reputation: 425083

Yes, it can be done. Use a left join with calls to ifnull() to do the overwriting:

select
    ifnull(d.time, r.time) as time,
    ifnull(d.title, r.title) as title,
    ifnull(d.desc, r.desc) as desc
from recurring_events r
left join dynamjc_events d on dayofweek(d.date) = r.day_of_week
    and d.time = r.time

Note that "overwrite" is not entirely possible because the columns are different, however you should select whatever columns you need from either table and they will be null for dynamic events if there's no match.

Upvotes: 1

spencer7593
spencer7593

Reputation: 108420

Here's one approach:

SELECT IF(d.id IS NOT NULL,'d','r')         AS dynamic_or_recurring
     , IF(d.id IS NOT NULL,d.id,r.id)       AS id
     , IF(d.id IS NOT NULL,d.title,r.title) AS title
     , ...
  FROM recurring_events r
  LEFT
  JOIN dynamic_events d
    ON d.time = t.time AND DAYOFWEEK(d.date) = r.day_of_week

The SELECT list could include just the columns from each table, and depending on the returned indicator, you would pick out which set of columns you need...

SELECT IF(d.id IS NOT NULL,'d','r')      AS dynamic_or_recurring
     , d.id                              AS d_id
     , d.date                            AS d_date
     , d.time                            AS d_time
     , ...
     , r.id                              AS r_id
     ,

Upvotes: 0

Related Questions