0

I want to select a name and a year. Why I get both year although I select 2020?

CREATE TABLE [dbo].[Person_Table]
    (
      Person nvarchar(500) NULL, 
      Name2 nvarchar(500), 
      Year_N int
    ) ON [PRIMARY]

INSERT INTO Person_Table (Person, Name2, [Year_N])
VALUES ('Ken,Brian,Mike,','A,B,','2000'),
       ('Mikem,Briane,Kenny,','C,D,','2020'),
       ('Dodo,Chris,Mike,','A,B,','2020');

-------------------------------------------------
DECLARE @Person NVARCHAR(100) = 'Mike',
        @Date int = 2020

SELECT *
FROM [dbo].[Person_Table]
WHERE EXISTS 
              (Select * from string_split(Person,',') 
               WHERE value in (@Person))
              
OR EXISTS     (Select * from string_split(Name2,',') 
               WHERE value in (@Person))

AND Year_N = @Date

Microsoft SQL Server 2019

MisterXAGE_
  • 27
  • 6
  • 3
    Because of your `OR`. Wrap the `OR` clauses in parenthesis and you get the expected results. Though what you should *really* being doing is fixing your design; don't store delimited data in your database. – Thom A Aug 18 '21 at 10:02
  • Agreed, this structure should be represented by at least 2 different tables, maybe 3. Learn about database design, normalisation and relationships so you can implement a usable structure. – ADyson Aug 18 '21 at 10:03
  • `OR` in SQL is different to English. In SQL, `OR` is logical disjunction, in English "or" (in this context) is a logical injunction ( https://en.wikipedia.org/wiki/Logical_conjunction ) – Dai Aug 18 '21 at 10:03
  • Try `where (exists (...) or exists(...)) and year = ...` – Bohemian Aug 18 '21 at 10:03
  • @Bohemian thank you man. You solve it. – MisterXAGE_ Aug 18 '21 at 10:08
  • @MisterXAGE_ Welcome to the site! We’re all here to help each other. – Bohemian Aug 18 '21 at 10:14

0 Answers0