title: 2 tables, one with dayofweek and id, the other with date and id mysql query
body: <p>I am fairly new to mysql so please be gentle.  I have tried to wrap my brain around this task and just don't know where to go.  At this point my brain is mush and I am looking for guidance.</p>
  
  <p>I have 2 tables:  shows and performances. </p>
  
  <p>Shows has showid and is based on a day of the week.  So, a Monday show has dayid 1, Tuesday show has dayid 2, etc.</p>
  
  <p>Performances on the other hand are specific dates the shows happen.  So the showid is included in performances and there is a dateofshow.</p>
  
  <p>What I am trying to do, for example, is list all the monday shows and match with all the performances that have been recorded and indicate which shows have been reported.</p>
  
  <p>So, If I have 3 Monday shows and only 2 performances that correspond to the showid have been reported.  Something like below...</p>
  
  <pre><code>+--------+-------+-------+------------+----------+
  | showid | dayid | show  | dateofshow | reported |
  +--------+-------+-------+------------+----------+
  |      1 |     1 | show1 | 9/07/2015  | Yes      |
  |      2 |     1 | show2 | NULL       | NO       |
  |      3 |     1 | show3 | 9/07/2015  | Yes      |
  +--------+-------+-------+------------+----------+
  </code></pre>
  
  <p>mysql...</p>
  
  <pre><code>SELECT s.*, pr.*
              FROM shows as s             
              LEFT JOIN performances as pr
              ON s.showid = pr.showid
  
              WHERE s.dayid = 1 
  </code></pre>
  
  <p>Again, thanks for any help.  My brain is fried.</p>
  
  <p>UPDATED: I didn't discuss this earlier but, the dateofshow has to be within the current week.  I have changed the date within the question to better suit the circumstances.  In the above "table" you will notice the shows reported are for 9/7/2015 which is a monday.  All 3 of these shows have performances already reported for 8/31/2015 which is the week prior.  So again, I am looking for dateofshow that is in the current week is "yes" and if dateofshow is only a prior date then "no".  I hope this helps and I do believe we are getting there.  Thanks again for all your help.</p>
  
  <p>I have tried this... </p>
  
  <pre><code>SELECT s.showid, s.dayid, s.venueid, v.venue, v.venueid,
     pr.dateofshow, IF(pr.dateofshow = DATE('2015-09-07'), 'Yes', 'No') as reported FROM shows as s JOIN venues as v  ON s.venueid = v.venueid LEFT JOIN performances as pr
       ON s.showid = pr.showid
    WHERE s.dayid = 1 
  </code></pre>
  
  <p>It provides me with 'yes' for shows with performances on the set date. and provides 'no' for shows with performances on a date prior to the set date.  Now I need to eliminate those that are earlier then the set date and do if a performance does not exist with the set date then 'no'.</p>