This is really a 2 part question. The first part is really a pure SQL question that can't seem to answer and the second part a question on how to create a scope from the answer to part 1 (providing it can be answered).
I currently have two tables: -
company_department_name is a normalisation table and ref_departments the reference table as the FK/PK shows.
In my create/update form, I have a dropdown to provide a list in the departmant_id field of the ref_departments ID and department_name. That's the easy bit.
However, I want to filter the data in the dropdown a bit more. Specifically, I want to filter it so that is only brings back options in the department_id field for those that are not already in use. The two fields it needs to filter the data on are company_id and department_id. For company_id, the company_id will be taken from $model->company_id. For the department_id, it needs to select where the department_id IS NOT NULL.
I am currently trying to produce the SQL statement with db::createCommand and have this so far: -
"SELECT RD.id, RD.department_name FROM ref_departments RD LEFT OUTER JOIN company_department_norm CDN ON (CDN.department_id = RD.id) WHERE (CDN.department_id IS NULL)"
This itself takes care of filtering the right table (company_department_norm) to make sure only rows from ref_departments whose PK is NULL in company_department_norm.department_id are returned. However, no matter what I try, I cannot seem to filter on the 2nd condition - that the company ID is matched so the query returns only rows where the the company_department_norm.department_id is NULL AND company_department_norm.company_id is a given value. For testing purposes, I have been using a company_id of integer 1, but no matter what I do it does not seem to filter the date.
I attempted this: -
LEFT OUTER JOIN (SELECT * FROM company_department_norm WHERE company_department_norm.company_id = 1) CDN
... but to no avail.
I assumed some kind of subquery is what is needed here but I have little or no experience with them and I am a bit stuck here. If/when I have a solution to the above, I will then try to create a named scope from it, which will make life a bit easier and my code a bit cleaner.
Can anyone help?