Stuck on this union / except

Question

Trying to find a better way to do this, for some reason, it really disconnects me.

I have the following data:

transaction_id(pk) decision_id(pk) accepted_ind A 1 NULL A 2 <blank> A 4 Y B 1 <blank> B 2 Y C 1 Y D 1 N D 2 O D 3 Y 

• In every transaction, solution 1 is guaranteed.
• There are several options for decision-making scenarios (something).
• Accepted can have several values ​​or be empty or NULL, but only one can be accepted_ind = Y

I am trying to write a query:

• Returns one row for each id_ transaction
• Returns solution_id, where accept_ind = Y or if the transaction has no lines accept_ind = Y, and then returns a row using solution_id = 1 (regardless of the value in accept_ind)

I tried: 1. Using a logical “or” to pull out the records, they continued to get duplicates. 2. The use of the union and the exception, but not quite the correct receipt of logic.

Any help is appreciated. I don’t know why it disconnects me so much!

Adam

Answer 1

Try it. Basically the WHERE says:

Where accepted = 'Y'
OR
There is no accepted row for this transaction, and solution_id = 1

 SELECT Transaction_id, Decision_ID, Accepted_id FROM MyTable t WHERE Accepted_ind = 'Y' OR (NOT EXISTS (SELECT 1 FROM MyTable t2 WHERE Accepted_ind = 'Y' and t2.Transaction_id = t.transaction_id) AND Decision_id = 1) 

Answer 2

This approach uses ROW_NUMBER() and therefore will only work on SQL Server 2005 or later.

I changed your sample data as it is, all transaction_id have indicator Y !

 DECLARE @t TABLE ( transaction_id NCHAR(1), decision_id INT, accepted_ind NCHAR(1) NULL ) INSERT @t VALUES ( 'A' , 1 , NULL ), ( 'A' , 2 , '' ), ( 'A' , 4 , 'Y' ), ( 'B' , 1 , '' ), ( 'B' , 2 , 'N' ), -- change from your sample data ( 'C' , 1 , 'Y' ), ( 'D' , 1 , 'N' ), ( 'D' , 2 , 'O' ), ( 'D' , 3 , 'Y' ) 

And here is the request itself:

 SELECT transaction_id, decision_id, accepted_ind FROM ( SELECT transaction_id, decision_id, accepted_ind, ROW_NUMBER() OVER ( PARTITION BY transaction_id ORDER BY CASE WHEN accepted_ind = 'Y' THEN 1 WHEN decision_id = 1 THEN 2 ELSE 3 END ) rn FROM @t ) Raw WHERE rn = 1 

Results:

 transaction_id decision_id accepted_ind -------------- ----------- ------------ A 4 Y B 1 C 1 Y D 3 Y 

The ROW_NUMBER() gives a "priority" to each criterion you specify; we then ORDER BY select the best and take the first row.

Answer 3

Probably a faster / more efficient request, but I think this will do the job. The table name is assumed to be the Solution:

  SELECT CASE WHEN accepteddecision.transaction_id IS NOT NULL THEN accepteddecision.transaction_id ELSE firstdecision.transaction_id END AS transaction_id, CASE WHEN accepteddecision.decision_id IS NOT NULL THEN accepteddecision.decision_id ELSE firstdecision.decision_id END AS decision_id, CASE WHEN accepteddecision.accepted_ind IS NOT NULL THEN accepteddecision.accepted_ind ELSE firstdecision.accepted_ind END AS accepted_ind FROM decision LEFT OUTER JOIN (SELECT * FROM decision AS accepteddecision WHERE accepteddecision.accepted_ind = 'Y') AS accepteddecision ON accepteddecision.transaction_id = decision.transaction_id LEFT OUTER JOIN (SELECT * FROM decision AS firstdecision WHERE firstdecision.decision_id = 1) AS firstdecision ON firstdecision.transaction_id = decision.transaction_id GROUP BY accepteddecision.transaction_id, firstdecision.transaction_id, accepteddecision.decision_id, firstdecision.decision_id, accepteddecision.accepted_ind, firstdecision.accepted_ind 

Answer 4

Of interest, the following UNION and EXCEPT (plus a JOIN ) are used, as indicated in the title of the question:

 WITH T AS (SELECT * FROM ( VALUES ('A', 1, NULL), ('A', 2, ''), ('A', 4, 'Y'), ('B', 1, ''), ('B', 2, 'Y'), ('C', 1, 'Y'), ('D', 1, 'N'), ('D', 2, 'O'), ('D', 3, 'Y'), ('E', 2, 'O'), -- smaple data extended ('E', 1, 'N') -- smaple data extended ) AS T (transaction_id, decision_id, accepted_ind) ) SELECT * FROM T WHERE accepted_ind = 'Y' UNION SELECT T.* FROM ( SELECT transaction_id FROM T WHERE decision_id = 1 EXCEPT SELECT transaction_id FROM T WHERE accepted_ind = 'Y' ) D JOIN T ON T.transaction_id = D.transaction_id AND T.decision_id = 1;