How to select data in t-sql

• VM-723206

  SSCrazy

  Points: 2964

  More actions

  March 11, 2009 at 12:30 am

  #957281

  create table test (id int,countries varchar(25))

  insert into test values(100,'IN, AU, GB, FR')

  insert into test values(101,'GB, FR, IN')

  insert into test values(102,'IN, AU')

  insert into test values(103,'AU, GB')

  insert into test values(104,'GB, FR, IN, AU')

  If it's the above table, the query below will help:

  select id from test where countries like ('%AU%') and countries like ('%IN%')

• Ivanna Noh

  SSCarpal Tunnel

  Points: 4138

  More actions

  March 11, 2009 at 5:16 pm

  #957993

  here's an even simpler query that those i've seen posted - but no doubt i have overlooked something 🙂

  CREATE TABLE #Test

  (ID INT, Countries CHAR(2));

  INSERT INTO #Test

  (ID, Countries)

  SELECT '100','IN' UNION ALL

  SELECT '100','AU' UNION ALL

  SELECT '100','GB' UNION ALL

  SELECT '100','FR' UNION ALL

  SELECT '101','GB' UNION ALL

  SELECT '101','GB' UNION ALL

  SELECT '101','FR' UNION ALL

  SELECT '101','IN' UNION ALL

  SELECT '102','IN' UNION ALL

  SELECT '102','AU' UNION ALL

  SELECT '103','AU' UNION ALL

  SELECT '103','GB' UNION ALL

  SELECT '104','GB' UNION ALL

  SELECT '104','FR' UNION ALL

  SELECT '104','IN' UNION ALL

  SELECT '104','AU'

  query:

  SELECT ID FROM #test WHERE countries LIKE 'IN'

  AND ID IN(SELECT ID FROM #test WHERE countries LIKE 'AU')

  did i miss something?:)

• Jeff Moden

  SSC Guru

  Points: 1004340

  More actions

  March 11, 2009 at 7:33 pm

  #958028

  Heh... nope... see above where I posted...

  SELECT ID

  FROM #Test

  WHERE Countries IN ('IN','AU')

  GROUP BY ID

  HAVING COUNT(*) = 2

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  March 11, 2009 at 10:49 pm

  #958073

  Ivanna Noh (3/11/2009)

  ---

  here's an even simpler query that those i've seen posted - but no doubt i have overlooked something 🙂

  ...

  query:

  SELECT ID FROM #test WHERE countries LIKE 'IN'

  AND ID IN(SELECT ID FROM #test WHERE countries LIKE 'AU')

  did i miss something?:)

  Yes, you missed the first reply (mine) which your query is identical too (except for your superfluous use of the LIKE operator).

  [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
  Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

• jmucherla

  SSC Enthusiast

  Points: 164

  More actions

  March 17, 2009 at 8:39 pm

  #961536

  Thank you all for your responses/suggestions. I'll make changes as required.