title: Adding a column to a select statement based on the existence of a record in another table
body: <p>I am writing a SQL query that I would like to add a column to the recordset which has a value based on the existence of a record in another table.  I have a left join joining the tables, and I am assuming I have to do some sort of pivot in my SQL, but I am not familiar with table pivoting.</p>
  
  <p>My existing SQL is</p>
  
  <pre><code>SELECT tabs.name,tabs.id AS tabid,tabs.sort,fields.id AS fieldid, fields.label
  FROM tabs
  INNER JOIN fields
    ON tabs.id = fields.tabid
  LEFT JOIN fields_reports
    ON fields_reports.fieldid = fields.id
  WHERE fields_reports.reportid = 57
  GROUP BY fields.id
  ORDER BY tabs.sort, fields.id
  </code></pre>
  
  <p>What happened in the SQL is that it pulls field (which is the core of the statement) and the tabs (which are essentailly categories).  The fields_reports table maps fields to reports that I am building.</p>
  
  <p>What I need to do is add a column to my statement that says: if the current field has a record in the fields_reports table with the report number passed in (57) then assign the column value of 1, else 0.</p>
  
  <p><strong>Edit:</strong>  I have another issue with the query.  Right now the query is only pulling fields attached to one report.  Instead of using a case is there a way that I can do a subquery to select from the fields_reports table so that I can pull all fields and then have the column attaching it to a report?</p>
  
  <p>This is the query that pulls the records now, but only pulls one reports fields</p>
  
  <pre><code>SELECT tabs.name,tabs.id AS tabid,tabs.sort,fields.id AS fieldid, fields.label,
    CASE WHEN fields_reports.id IS null THEN 0 ELSE 1 END AS inReport
  FROM fields
  INNER JOIN tabs
    ON tabs.id = fields.tabid
  LEFT JOIN fields_reports
    ON fields_reports.fieldid = fields.id
  WHERE fields_reports.reportid = 57
  GROUP BY fields.id
  ORDER BY tabs.sort, fields.id
  </code></pre>
  
  <p>Let me know if I should open up a new question for this.</p>