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

Question

I am writing an SQL query that would like to add a column to a recordset that has a value based on the existence of a record in another table. I have a left join joining tables, and I assume that I need to do some kind of rotation in my SQL, but I am not familiar with the rotation of the table.

My existing SQL

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 

What happened in SQL is that it pulls out a field (which is the core of the instruction) and tabs (which are essentailly categories). The fields_reports table maps the fields to the reports that I create.

What I need to do is add a column to my statement that says: if the current field has an entry in the fields_reports table with the report number passed to (57), then assign the value of column 1, otherwise 0.

Edit: I have another problem with the request. At the moment, the request only pulled the fields attached to one report. Instead of using a case, is there a way I can make a subquery to select fields_reports from the table so that I can pull out all the fields and then add a column to the report?

This is a query that now retrieves records, but only pulls out one report field

 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 have to open a new question for this.

Answer 1

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

EDIT The test for reportid in the WHERE clause must be deleted, otherwise it does not make sense as a solution. (Thanks @Dave Long for this.)

Answer 2

See what I did with 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 

Notice something else? I omitted the WHERE and translated the condition into the ON LEFT JOIN fields_reports . WHERE , as indicated, turned your LEFT JOIN into an INNER JOIN, so the result set would try to select rows with fields_reports.reportid = 57 . If there were no such rows, none of them would be returned, i.e. You could not use the column of your flag.

But now it can be defined, for example, as follows:

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

Just add it to your picklist.

Answer 3

The phrase will be:

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

this works because when you leave the join and no row exists in the joined table, the columns are still present in the result, but they are all zero. So you can check this null value to see if the line on the right side of the connection matches you.