Select record from table 1 where does not exist in table 2

I have a table with a list of surveys - tbl_survey

I have a table of campaigns - tbl_campaign

I have a table which links the surveys to campaigns - map_survey_to_campaign

What I need is to be able to retrieve a list of surveys from tbl_survey which have not been assigned to a campaign

Here is my current code:

$surveyList = Survey::find()





The query is working if I set the null to a known campaign_id, so I know the syntax is correct. Somehow I need to replace the "where" with "Where campaign x does not exist in map_survey_to_campaign.fk_campaignID"

How can I do that?

Many thanks

In case anybody else needs to find the solution, here it is:

You need to perform a subquery as follows (copied from another website):

$subQuery = Follower::find()->select('foreignKey_Leader');

$query = Leader::find()->where(['not in', 'leaderID', $subQuery]);

$models = $query->all();

Key points:


[*]You do NOT add "->all()" to the initial subquery, instead you leave the query open

[*]You must select one column and only one column in the subquery, typically the foreign key column

[*]There is no need to define the association between the query and the subquery. The association is implied because there is only one column in the subquery.


The result of the query above would return all leaders without a follower.

I hope this helps somebody else. It took me a whole day to figure this out!