|
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; |
|
I would change WHERE (SELECT Submission_ID FROM Submissions WHERE Agent_ID = ID AND Novel_ID = 3) is nullinto 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. 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 :-)
(19 Mar, 03:04)
gchq
|
|
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; Thanks for that - I'm in a different time zone at the moment and it's late - will try that on the 'morrow
(18 Mar, 14:20)
gchq
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
(19 Mar, 02:29)
gchq
|
|
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 |