Adding a column to a select statement based on the existence of a record in another table

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.

My existing SQL is

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
OR开发者_如何学JAVADER BY tabs.sort, fields.id

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.

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.

Edit: 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?

This is the query that pulls the records now, but only pulls one reports fields

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

Let me know if I should open up a new question for this.

You can `SELECT ..., IF(field_reports.reportid=57),1,0), ... FROM ...`

EDIT The test on reportid in the WHERE clause will need to be removed, or else this makes little sense as a solution. (Thanks to @Dave Long for this.)

Look what I've done to your script:

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.id = fields.id AND fields_reports.reportid = 57
GROUP BY fields.id
ORDER BY tabs.sort, fields.id

Noticed something different? I dropped the WHERE clause and moved the condition to the ON clause of LEFT JOIN fields_reports. WHERE, as it was specified, turned your LEFT JOIN into INNER JOIN, so the result set would only try to select the rows with fields_reports.reportid = 57. If there were no such rows, none would be returned, i.e. that way you couldn't have your flag column working.

But now it can be defined, for example, like this:

MAX(CASE fields_reports.reportid WHEN 57 THEN 1 ELSE 0 END) AS TheFlagColumn

Just append it to your select list.

The phrase would be:

select ....
     , case when LeftJoinedTable.column is null then 0 else 1 end
  from ....

this works because, when you left join and no row exists in the joined table, the columns are still present in the result but they are all null. So you can test for this null value to see if you matched to a row in the right side of the join.