Make a selection based on second join not returning any results.

[former_SQLA_member1694879]

I have this example of a simple left join. It's put together in an app based on a host of variables and now I need to check another table to make sure an action has not been taken, otherwise it will be duplicated.

SELECT Publisher_Contacts.Contact_ID as 'ID', 
STRING(Publisher_Contacts.Contact_FirstName, ' ', 
Publisher_Contacts.Contact_Surname) as 'Name', 
Publishers.Publisher_Name as 'Agency' 
FROM Publisher_Contacts 
LEFT JOIN Publishers ON Publisher_Contacts.Publisher_ID = Publishers.Publisher_ID 
WHERE Publisher_Contacts.Flag_IsLive = 1 
AND Publisher_Contacts.Flag_IsAgent = 1 
AND Publisher_Contacts.Flag_IsQueryManager = 0 
AND Publisher_Contacts.Flag_IsSubmittable = 0 
AND Publisher_Contacts.Flag_IsOnlineForm = 0 
AND Publisher_Contacts.Flag_IsAttachment = 0 
ORDER BY Publisher_Contacts.Contact_Surname

The results end up in a datagrid - once they are selected and an action taken another table will have an INSERT that will contain the Contact_ID and the ID of the action taken - after that the data will be reloaded but to exclude the items that have been run - hope this makes some sense - it's been a long day and I can't think of method of adding another join where I want to exclude the result.

Sorry - I was half asleep - I think this will work

BEGIN 
DECLARE IsValid int;
SET IsValid = (SELECT COUNT(Submission_ID) FROM Submissions WHERE Agent_ID = 1 and Novel_ID = 3);
IF IsValid = 0 THEN
SELECT Publisher_Contacts.Contact_ID as 'ID', 
STRING(Publisher_Contacts.Contact_FirstName, ' ', 
Publisher_Contacts.Contact_Surname) as 'Name', 
Publishers.Publisher_Name as 'Agency' 
FROM Publisher_Contacts 
LEFT JOIN Publishers ON Publisher_Contacts.Publisher_ID = Publishers.Publisher_ID 
WHERE Publisher_Contacts.Flag_IsLive = 1 
AND Publisher_Contacts.Flag_IsAgent = 1 
AND Publisher_Contacts.Flag_IsQueryManager = 0 
AND Publisher_Contacts.Flag_IsSubmittable = 0 
AND Publisher_Contacts.Flag_IsOnlineForm = 0 
AND Publisher_Contacts.Flag_IsAttachment = 0 
ORDER BY Publisher_Contacts.Contact_Surname
END IF;

END;

[former_SQLA_member1694879 · Accepted solution]

I got there (I think) - thank you Volker for pointing me in the right direction.

SELECT Publisher_Contacts.Contact_ID as 'ID', 
STRING(Publisher_Contacts.Contact_FirstName, ' ', 
Publisher_Contacts.Contact_Surname) as 'Name', 
Publishers.Publisher_Name as 'Agency' 
FROM Publisher_Contacts 
LEFT JOIN Publishers ON Publisher_Contacts.Publisher_ID = Publishers.Publisher_ID 
LEFT JOIN Submissions ON Submissions.Agent_ID = Publisher_Contacts.Contact_ID 
WHERE (SELECT Submission_ID FROM Submissions WHERE Agent_ID = ID AND Novel_ID = 3) is null
AND Publisher_Contacts.Flag_IsLive = 1 
AND Publisher_Contacts.Flag_IsAgent = 1 
AND Publisher_Contacts.Flag_IsQueryManager = 0 
AND Publisher_Contacts.Flag_IsSubmittable = 0 
AND Publisher_Contacts.Flag_IsOnlineForm = 0 
AND Publisher_Contacts.Flag_IsAttachment = 0 
ORDER BY Publisher_Contacts.Contact_Surname