Inner query returns more than one row

Question

Inner query returns more than one row

Data Cruncher

SSC-Addicted

Points: 499
More actions
July 3, 2018 at 9:23 am

#353560

Hi folks, I have a problem that I don't know how to solve: the subquery returns more than one row.
Table 1:
|Id | Flag | T2_FK |
-------------------
| 1 | F1 | L1 |
-------------------
| 2 | F1 | L2 |
-------------------
| 3 | NULL | L1 |
-------------------
| 4 | NULL | L3 |
Table 2:
|Id | Descrip |
----------------
| L1 | Descr1 |
----------------
| L2 | Descr2 |
----------------
| L3 | Descr3 |
----------------
| L4 | Descr4 |
Desired result:
|Id | Flag | Descr |
----------------------
| 1 | F1 | Descr1 |
----------------------
| 2 | F1 | Descr2 |
----------------------
Query:
select Id, Flag,
case when Flag = 'F1' then (select Descrip from Table2 inner join Table1 on Table2.Id = Table1.T2_FK where Table1.Id = extT1.Id) end Descr
from Table1 as extT1
What am I doing wrong?
Note: this is only a small part of a much bigger query so I should handle this with a case statement

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Well, using what you posted (and yes, I had to write the SQL you should have posted) I don't get your error. I also don't get your expected results, but that can be fixed.
CREATE TABLE [dbo].[table1]( [Id] [INT] NOT NULL , [Flag] [CHAR](2) NULL , [T2_FK] [CHAR](2) NOT NULL); GO CREATE TABLE [dbo].[table2]( [Id] [CHAR](2) NOT NULL , [Descrip] [VARCHAR](32) NOT NULL); GO

INSERT INTO [dbo].[table1]([Id],[Flag],[T2_FK])
VALUES (1,'F1','L1'),(2,'F1','L2'),(3,NULL,'L1'),(4,NULL,'L3');

INSERT INTO [dbo].[table2]([Id],[Descrip])
VALUES ('L1','Descr1'),('L2','Descr2'),('L3','Descr3'),('L4','Descr4');

SELECT
[Id]
, [Flag]
, CASE WHEN [Flag] = 'F1'
         THEN (SELECT
                 [Descrip]
               FROM
                 [table2]
                 INNER JOIN [table1]
                   ON [table2].[Id] = [table1].[T2_FK]
               WHERE
                 [table1].[Id] = [extT1].[Id]
        )
    END [Descr]
FROM
[table1] AS [extT1];
GO

DROP TABLE [dbo].[table1];
DROP TABLE [dbo].[table2];
GO

Data Cruncher SSC-Addicted Points: 499 More actions · Answer 2

Hi Lynn,
first of all, my sincere apologies for not posting the question in a better fashion, I'll try to improve next time I post a question.
Thank you for your answer, so it seems the issue is elsewhere. I'll dig deeper and get back to you.
Best wishes

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 3

Does the ID column of Table1 have unique values? If not, that could be a potential problem since you join Table1 to itself in the subquery. If it is unique, then you probably don't need the extra join to Table1, just correlate Table2 in subquery directly to ExtT1.

Does the ID column of Table2 have unique values? If so this should work:
select Id, Flag, case when Flag = 'F1' then (select Descrip from Table2 where Table2.Id = extT1.T2_FK) end Descr from Table1 as extT1

If not then maybe you need some other condition to properly query Table2, maybe by an effective date or something?
select Id, Flag, case when Flag = 'F1' then (select Descrip from Table2 where Table2.Id = extT1.T2_FK) end Descr from Table1 as extT1 outer apply (select top 1 Descrip from Table2 where Table2.Id = extT1.T2_FK order by effective_date desc)

Data Cruncher SSC-Addicted Points: 499 More actions · Answer 4

@Chirs yes, the IDs are unique but thanks for the hint regarding the join-free correlation, that way the query is much more readable.

After investigating the issue I finaly got it:
- the initial case condition was wrong and therefore the whole query returned an unexpected result. The condition was checking a N-th bit of a flag column using bitwise operators.

Thank you both!