Geek asks and answers

Simple sql query refactoring

I have a table with rows:

ID          CountryCode Status
----------- ----------- -----------
2           PL          1
3           PL          2
4           EN          1
5           EN          1

and upon request

SELECT *
  FROM [TestTable]
  WHERE Status = 1 AND CountryCode NOT IN (SELECT CountryCode
  FROM [TestTable]
  WHERE Status != 1)

I get all national codes that have no status value = 2

ID          CountryCode Status
----------- ----------- -----------
4           EN          1
5           EN          1

I feel this request could be simpler and more understandable.

How can I change it?

Regards

EDIT

PL cannot be the result because it has a record with status 2

EDIT

Script to create and populate a table:

USE [DatabaseName]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TestTable](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [CountryCode] [nvarchar](2) NOT NULL,
    [Status] [int] NOT NULL
) ON [PRIMARY]

INSERT INTO dbo.TestTable
          ( CountryCode, Status )
  VALUES  ( 'PL', -- CountryCode - nvarchar(2)
            1  -- Status - int
            )

INSERT INTO dbo.TestTable
          ( CountryCode, Status )
  VALUES  ( 'PL', -- CountryCode - nvarchar(2)
            2  -- Status - int
            )

INSERT INTO dbo.TestTable
          ( CountryCode, Status )
  VALUES  ( 'EN', -- CountryCode - nvarchar(2)
            1  -- Status - int
            )
INSERT INTO dbo.TestTable
          ( CountryCode, Status )
  VALUES  ( 'EN', -- CountryCode - nvarchar(2)
            1  -- Status - int
            )
+3
source share
4 answers

First: never use SELECT *in frequently used code. Especially in production. Call up your columns.

Soap box.

Note. I have not tried this, and currently I do not have a management studio, so I can not test it. But I think you want something like this:

Select Id, CountryCode, Status
From [TestTable] t
Where Status <> 2
And Not Exists(select status from [TestTable] t2 
                             where t2.Status = 2 
                             and t2.CountryCode = tt.CountryCode)

, : , CountryCodes, ( ), 2, 1, 2. Not Exists.

+7
select T1.*
from TestTable as T1
  left outer join
    (
      select distinct CountryCode
      from TestTable as T1
      where Status <> 1  
    ) as T2
    on T1.CountryCode = T2.CountryCode
where
  T1.Status = 1 and
  T2.CountryCode is null
+3

If you want all entries in which it Statusdoes not matter 2, try the following:

SELECT *
  FROM [TestTable]
 WHERE Status != 2

EDIT: To prevent the code of the country where any particular entry has an undesirable value, try suggestions GROUP BYand HAVING:

  SELECT CountryCode
    FROM [TestTable]
GROUP BY CountryCode
HAVING MAX(Status) = 1 AND MIN(Status) = 1
+1
source 
 SELECT distinct(country) FROM table WHERE value <> 2
0
source

More articles: