SQL Select

• Dee Dee-422077

  Ten Centuries

  Points: 1082

  More actions

  June 29, 2017 at 10:07 pm

  #322336

  Hi,
  when I have 1 item which have multiple codes some codes have status check, some don't.
  Table 1
  Item         Code
  Item1        code1
  Item1        code2
  item2        code3
  Item2        code4

  Table 2
  Code              Status
  Code1            1
  Code2           0
  code3            0
  code4           0

  One Item can have multiple codes,
  How can I write a SQL statement to select the item that have none of the code status mark as 1.

  select result should be:
  Item 2  code 3  0
  Item2    code4  0

  Please advise.

  Thanks,
  Ddee

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  June 30, 2017 at 2:27 am

  #1948881

  
CREATE TABLE #Table1 (Item VARCHAR(5), Code VARCHAR(5))
INSERT INTO #Table1 (Item, Code) VALUES
('Item1', 'code1'),
('Item1', 'code2'),
('item2', 'code3'),
('Item2', 'code4')

  CREATE TABLE #Table2 (Code VARCHAR(5), [Status] TINYINT)
  INSERT INTO #Table2 (Code, [Status]) VALUES
  ('Code1', 1),
  ('Code2', 0),
  ('code3', 0),
  ('code4', 0)

  -- this query returns Items which have a [Status] = 1 in #Table2
  SELECT ti1.Item
  FROM #Table1 ti1
  INNER JOIN #Table2 ti2
   ON ti1.Code = ti2.Code
  WHERE ti2.[Status] = 1

  -- So use it in a NOT EXISTS, like this:
  SELECT t1.*
  FROM #Table1 t1
  WHERE NOT EXISTS (
   SELECT 1
   FROM #Table1 ti1
   INNER JOIN #Table2 ti2
    ON ti1.Code = ti2.Code
   WHERE ti2.[Status] = 1
    AND ti1.Item = t1.Item
  )
  

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden