Databases
StackOverflow2013
Postsdbo
POMySQL - Simplify complex query for teacher/student subscribers
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL - Simplify complex query for teacher/student subscribers
    primarykey
    data
    text
    <p>For an online learning website, a teacher can create any courses they would like to teach. Each course has lessons. Students can then subscribe to these courses or any future courses the teacher adds if they choose to. Here are a three ways a student can subscribe to courses from a teacher: <br><br> 1. Subscribe to <strong>all courses</strong> only (All current and future by teacher) <strong>ex.</strong> subscriber_id 12<br> 2. Subscribe to <strong>all courses</strong> and then <strong>unsubscribe individual courses</strong> (Even if unsubscribed from all <br>&nbsp;&nbsp;&nbsp; current individual courses, they will still be subscribed to future ones) <strong>ex.</strong> subscriber_id 10 <br> 3. Subscribe to <strong>individual courses</strong> only <strong>ex.</strong> subscriber_id 11</p> <p>Here is what the subscribed_courses table looks like:</p> <pre><code>subscribed_courses (Table with all member chosen resort services) teacher_id subscriber_id course_id sub_type 4 10 0 all 4 10 1 unsub 4 10 2 unsub 4 11 4 sub 4 12 0 all </code></pre> <p>When a teacher adds a new course, or a lesson, we need to scan the subscribed_courses table, and see which subscribed students should get an email. The problem is getting the below query to work to get the correct subscriber_ids to email. For this specific query example, let's use course_id 2, which just got a new lesson added, and we need to email all subscribers. There must be a better way to accomplish this:</p> <pre><code>SELECT DISTINCT subscriber_id FROM subscribed_courses WHERE teacher_id='4' //AND #1 (sub_type='sub' AND course_id='2') //individually subscribed #2 ((sub_type='all' AND course_id='0') AND (sub_type='unsub' AND course_id&lt;&gt;'2')) //subscribed to all, ensure subscriber didn't unsub from this course [all subscribed 1 row + more row(s) for unsub] #3 (sub_type='all' AND course_id='0') //all subscribed [if this is one row only for subscriber] </code></pre> <p>Any idea how we can get the correct subscriber_ids in our situation or a better working solution?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMaverick
    1. USMaverick
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMySQL - Simplify complex query for teacher/student subscribers
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhat do you mean by the course_id=2 in the queries?
      singulars
      1. POMySQL - Simplify complex query for teacher/student subscribers
      1. USRGV
    2. COWe were emailing course_id 2 subscribers. Just added that to the question, thanks for noticing.
      singulars
      1. POMySQL - Simplify complex query for teacher/student subscribers
      1. USMaverick
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload