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;

asked 18 Mar, 11:59

gchq's gravatar image

gchq
421263241
accept rate: 27%

edited 18 Mar, 12:26


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.

permanent link

answered 19 Mar, 02:59

Frank%20Vestjens's gravatar image

Frank Vestjens
1.3k354765
accept rate: 21%

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;
permanent link

answered 18 Mar, 13:38

Volker%20Barth's gravatar image

Volker Barth
40.2k361549822
accept rate: 34%

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
permanent link

answered 19 Mar, 02:35

gchq's gravatar image

gchq
421263241
accept rate: 27%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×22

question asked: 18 Mar, 11:59

question was seen: 209 times

last updated: 19 Mar, 03:04