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

[fvestjens]

I would change

WHERE (SELECT Submission_ID FROM Submissions WHERE Agent_ID = ID AND Novel_ID = 3) is null

into

WHERE NOT EXISTS(SELECT 1 FROM Submissions WHERE Agent_ID = ID AND Novel_ID = 3)

in case the select set returns more than 1 row.

[former_SQLA_member1694879]

In theory that combo should not ever return more than one row, if it does something got messed up - but I will change that. Thank you for your input 🙂

[VolkerBarth]

In order to suppress rows that are already contained in another table T2 (or in my case, often in the same table I am going to insert into to suppress duplicates), I usually add a left join on that table T2 and check within the WHERE clause that T2.ID is null, such as

INSERT [...]
SELECT [...]
FROM [...]
   LEFT JOIN T2 on [whatsoever] = T2.ID
WHERE [...]
   AND T2.ID IS NULL;

[former_SQLA_member1694879]

Thanks for that - I'm in a different time zone at the moment and it's late - will try that on the 'morrow

[former_SQLA_member1694879]

The problem is I need to check the third table for a combination of two items together being null - T2.ID IS NULL partly works but T2.ID might well return a value, but not Novel_ID - in other words if SELECT * FROM Table3 WHERE Agent_ID = (the join ID) and Novel_ID = 5 returns NULL then it can be part of the overall SELECT