InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 05:04:32 GMT20http://www.sqlservercentral.com/Forums/Topic1397921-392-1.aspx[quote][b]Melanie Peterson (12/20/2012)[/b][hr]Thank you, Vinu! The EXCEPT statement is new to me. Always nice to learn something new.[/quote]Yes it is always good to learn something new Melanie. And what is even better is getting to know how to do things the right way. So, if this is not a one time requirement then I would recommend that you do a small test to get to know which of the above two solutions is better performance wise on a bigger set of data and use that one.Thu, 20 Dec 2012 22:23:55 GMTvinu512http://www.sqlservercentral.com/Forums/Topic1397921-392-1.aspxThank you, Vinu! The EXCEPT statement is new to me. Always nice to learn something new.Thu, 20 Dec 2012 08:25:25 GMTMelanie Petersonhttp://www.sqlservercentral.com/Forums/Topic1397921-392-1.aspxHere is another way of doing it:[code="sql"]Select a.ItemID, b.ActionID From ( Select Distinct ItemID From Items ) As a CROSS JOIN Actions As b Except Select ItemID, ActionID From Items[/code]Thu, 20 Dec 2012 01:47:38 GMTvinu512http://www.sqlservercentral.com/Forums/Topic1397921-392-1.aspx>> The Action Table contains actions that need to be completed for each Item and the person responsible for taking the action. It is essentially a lookup table. <,Remember the first week of your first database class? Keys?? How NULLs work? You can not ever have a key in this non-table! Can I assume that that the actions have to be done in some order? Why is one single employee an inherent attributive of an action? There should be a job assignment relationship with an employee and an action. CREATE TABLE Actions(action_seq INTEGER NOT NULL PRIMARY KEY, action_name VARCHAR(30) NOT NULL);There is no reason to write dialect insertions today: INSERT INTO Actions VALUES (1, 'Emails Sent'), (2, 'Emails Archived'), (3, 'Project Folder Archived'), (4, 'Acknowledged Notification');Do you want 1:1 , 1:m or m:1 relationship in employee:action? Add UNIQUE as needed. CREATE TABLE Job_Assignments(action_seq INTEGER NOT NULL, REFERENCES Actions (action_seq), responsible_emp_id INTEGER NOT NULL REFERENCES Personnel (emp_id), PRIMARY KEY (action_seq, responsible_emp_id));>> The Items table contains items for which each of the 4 actions must be completed. There can be a maximum of 4 rows per item, i.e., one row for each action. <<You really hate keys! Do this in the DDL: CREATE TABLE Items(item_id INTEGER NOT NULL, action_seq INTEGER NOT NULL CHECK (action_seq IN (1,2,3,4), PRIMARY KEY (item_id, action_seq));Again, use ANSI/ISO Standard SQL, not dialect. INSERT INTO Items VALUES (1, 1), (1, 2), (2, 4), (3, 1), (3, 2), (3, 3);>> I want a result set that reflects the uncompleted actions for each item_id <<SELECT X.* FROM (SELECT I1.item_id, A.action_seq -- all combinations FROM A.actions, Items AS I1 EXCEPT -- remove existing ones SELECT item_id, action_seq FROM Items) AS X(item_id, action_seq)Wed, 19 Dec 2012 20:01:03 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1397921-392-1.aspxI believe this does it.Thanks, Steve!Tue, 18 Dec 2012 11:51:33 GMTMelanie Petersonhttp://www.sqlservercentral.com/Forums/Topic1397921-392-1.aspxThere are probably better ways, but I think this works:[code="sql"]; WITH AllActions (ItemID, ActionID)AS(SELECT distinct i.itemid, a.actionidFROM items i CROSS APPLY ( select a.actionid FROM Actions a ) a )SELECT* FROM AllActions aaWHERE aa.itemid NOT IN (select i.itemid FROM items i WHERE aa.itemid = i.ItemID AND aa.ActionID = i.ActionID )ORDER BY itemid , ActionID [/code]Tue, 18 Dec 2012 11:47:27 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic1397921-392-1.aspxOK, sorry, let me try to explain more fully. The Action Table contains actions that need to be completed for each Item and the person responsible for taking the action. It is essentially a lookup table. So:[code="sql"]CREATE TABLE Actions( ActionID int, ActionName varchar(30),, EmployeeResponsible int);goINSERT Actions SELECT 1, 'Emails Sent', 12345;INSERT Actions SELECT 2, 'Emails Archived', 67890;INSERT Actions SELECT 3, 'Project Folder Archived', 54321;INSERT Actions SELECT 4, 'Acknowledged Notification', 09876;go[/code]The Items table contains items for which each of the 4 actions must be completed. There can be a maximum of 4 rows per item, i.e., one row for each action. So:[code="sql"]CREATE TABLE Items( ItemID int, ActionID int);goINSERT Items SELECT 1, 1;INSERT Items SELECT 1, 2;INSERT Items SELECT 2, 4;INSERT Items SELECT 3, 1;INSERT Items SELECT 3, 2;INSERT Items SELECT 3, 3;GO[/code]So, for Item 1, Actions 1 and 2 have been completed, but not Actions 3 and 4. For Item 2, only Action 4 has been completed. For Item 3 all actions except Action 4 have been completed. I want a result set that reflects the uncompleted action IDs for each ItemID:ItemID ActionID1 31 42 12 22 33 4Thanks!Tue, 18 Dec 2012 11:17:34 GMTMelanie Petersonhttp://www.sqlservercentral.com/Forums/Topic1397921-392-1.aspxYou are missing some data here. How can you tell an actionid for an item has been completed when the first table doesn't have ItemIDs? You have two actionIds of "1" for different items.Please also ask the question with DDL like this, so people can help you run tests.[code="sql"]CREATE TABLE ActionEmp( ActionID int, EmployeeResponsible int);goINSERT ActionEmp SELECT 1, 12345;INSERT ActionEmp SELECT 2, 67890;INSERT ActionEmp SELECT 3, 54321;INSERT ActionEmp SELECT 4, 09876;goCREATE TABLE Actions( ItemID int, ActionID int);goINSERT Actions SELECT 1, 1;INSERT Actions SELECT 1, 2;INSERT Actions SELECT 2, 4;INSERT Actions SELECT 3, 1;INSERT Actions SELECT 3, 2;INSERT Actions SELECT 3, 3;GOSELECT a.ItemID, ae.ActionID, ae.EmployeeResponsible from ActionEmp ae LEFT OUTER JOIN Actions a ON ae.actionid = a.actionid ORDER BY a.ItemID , ae.actionid; [/code]Tue, 18 Dec 2012 10:53:35 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic1397921-392-1.aspxI have two tables, a table of actions and a table of items. They look like this:ActionID EmployeeResponsible1 123452 678903 543214 09876ItemID ActionID1 11 22 43 13 23 3I need to get the ActionIDs that have not been completed for each Item. The part that's giving me problems is that I also need the ItemIDs. So my results should beItemID ActionID ResponsibleEmployee1 3 543211 4 098762 1 123452 2 678902 3 543213 4 09876Help, please :unsure:Tue, 18 Dec 2012 10:34:51 GMTMelanie Peterson