Multiple columns return in CASE Statement SQL

Question

I have two tables and based on the sum of a field in TABLE1 I have to return different datasets from TABLE2:

I am trying to achieve this through a Case statement but getting an error saying subselect must have only one field.

Is there a better way to do this? simply when the sum of a column in table1 is 0 do not select anything from table2

TABLE1:

TABLE2:

MY SQL:

SELECT 
   CASE 
   WHEN SUM(transaction_unit_failed) > 0 
   THEN (
         SELECT sale_event_nr, business_unit, transaction_nr, transaction_unit_failed_number
        FROM TABLE2
        )
   WHEN SUM(transaction_unit_failed) = 0 
   THEN (
         SELECT sale_event_nr, business_unit, transaction_nr, transaction_unit_failed_number
        FROM TABLE2
        WHERE 1 = 2
 
        )
FROM TABLE1

Please avoid posting images of data, we cannot make use data from an image, sample data should be consumable text in your question, ideally as create and insert statements, or alternatively a DB<>Fiddle — Stu
– Stu, Commented Dec 29, 2021 at 22:42

shawnt00 · Accepted Answer · 2021-12-29 23:26:59Z

2

select * from table2
where exists (
    select 1
    from table1
    having sum(transaction_unit_failed) > 0
);

Similarly:

select * from table2
where (
    select sum(transaction_unit_failed)
    from table1
) > 0;

https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=3f68d250bc9a3235767b86626092799e

You could certainly write it as a join if there were a compelling reason. It would eliminate the convenience of nicely using * to return only the columns from the one table.

select *
from table2 inner join (
    select sum(transaction_unit_failed) as stuf
    from table1
) on stuf > 0;

edited Dec 29, 2021 at 23:26

answered Dec 29, 2021 at 22:57

shawnt00

18.1k3 gold badges23 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Edouard · Accepted Answer · 2021-12-29 22:58:40Z

1

 SELECT sale_event_nr, business_unit, transaction_nr, transaction_unit_failed_number
   FROM TABLE2
  WHERE (SELECT SUM(transaction_unit_failed) > 0 
                     FROM TABLE1)

answered Dec 29, 2021 at 22:58

Edouard

7,1291 gold badge11 silver badges24 bronze badges

Collectives™ on Stack Overflow

Multiple columns return in CASE Statement SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related