Need Query Help Urgent

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  February 6, 2012 at 12:40 pm

  #1443385

  Jing that will work but I would take either of the two Jeff suggested. My first choice would have been EXCEPT if only because it is VERY easy to read. I decided to test all three to examine the execution plans. The two Jeff posted were identical and the one you posted has an extra table scan (due to the addition of a third subquery).

  select stdnt_name, stdnt_id

  from Student

  where stdnt_activity in (5, 10)

  group by stdnt_name, stdnt_id

  having COUNT(distinct stdnt_activity) = 2

  AND stdnt_id NOT IN (SELECT stdnt_id FROM Student WHERE stdnt_activity = 15)

  order by stdnt_name

  select stdnt_name, stdnt_id

  from Student

  where stdnt_activity in (5, 10)

  group by stdnt_name, stdnt_id

  having COUNT(distinct stdnt_activity) = 2

  EXCEPT

  SELECT stdnt_Name, stdnt_id FROM Student WHERE stdnt_activity = 15

  order by stdnt_name

  select stdnt_name, stdnt_id

  from student

  WHERE stdnt_id NOT IN (SELECT stdnt_id FROM student WHERE stdnt_activity =15 )

  and stdnt_id IN

  (select stdnt_id from student GROUP BY stdnt_id having COUNT(*)=2)

  group by stdnt_name, stdnt_id

  order by stdnt_name

  On a small dataset is probably doesn't make much difference. It was however interesting enough for me to investigate just to see. All things being equal i would stick with EXCEPT just for easy readability.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• Jeff Moden

  SSC Guru

  Points: 1004304

  More actions

  February 15, 2012 at 9:15 pm

  #1447635

  Jing. (2/6/2012)

  ---

  Then this will return only those students who had 5 AND 10 but not 15

  create table student ( stdnt_id int, stdnt_name varchar(50),stdnt_activity SMALLINT )

  go

  INSERT INTO student

  select 1, 'kiran', 5 UNION ALL

  select 1 ,'kiran', 10 UNION ALL

  select 1 ,'kiran', 15 UNION ALL

  select 2 ,'sachin', 5 UNION ALL

  select 2 ,'sachin', 10 UNION ALL

  select 3 ,'venkat', 5 UNION ALL

  select 3 ,'venkat', 10 UNION ALL

  select 3 ,'venkat', 15 UNION ALL

  select 4 ,'kumar', 5 UNION ALL

  select 4 ,'kumar', 10 UNION ALL

  select 5 ,'naveen', 5 UNION ALL

  select 5 ,'naveen', 10 UNION ALL

  select 6, 'test', 5 UNION ALL

  select 7, 'test2', 10 UNION ALL

  select 7, 'test2', 15

  select * from student

  WHERE stdnt_id NOT IN (SELECT stdnt_id FROM student WHERE stdnt_activity =15 )

  and stdnt_id IN

  (select stdnt_id from student GROUP BY stdnt_id having COUNT(*)=2)

  There are absolutely no constraints on the table that says the stdnt_activity is limited to only the values 5, 10, and 15. You cannot assume that other data won't enter the picture. If we make one small change to the data, your code breaks.

  drop table student

  create table student ( stdnt_id int, stdnt_name varchar(50),stdnt_activity SMALLINT )

  go

  INSERT INTO student

  select 1, 'kiran', 5 UNION ALL

  select 1 ,'kiran', 10 UNION ALL

  select 1 ,'kiran', 15 UNION ALL

  select 2 ,'sachin', 5 UNION ALL

  select 2 ,'sachin', 10 UNION ALL

  select 3 ,'venkat', 5 UNION ALL

  select 3 ,'venkat', 10 UNION ALL

  select 3 ,'venkat', 15 UNION ALL

  select 4 ,'kumar', 5 UNION ALL

  select 4 ,'kumar', 10 UNION ALL

  select 5 ,'naveen', 5 UNION ALL

  select 5 ,'naveen', 7 UNION ALL --<<<<-----

  select 6, 'test', 5 UNION ALL

  select 7, 'test2', 10 UNION ALL

  select 7, 'test2', 15

  select * from student

  WHERE stdnt_id NOT IN (SELECT stdnt_id FROM student WHERE stdnt_activity =15 )

  and stdnt_id IN

  (select stdnt_id from student GROUP BY stdnt_id having COUNT(*)=2)

  Naveen no longer has a 5 and a 10 with no 15 but still shows up in the result set. You need to bullet proof your code a bit.

  stdnt_id stdnt_name stdnt_activity

  ----------- ----------- --------------

  2 sachin 5

  2 sachin 10

  4 kumar 5

  4 kumar 10

  5 naveen 5

  5 naveen 7 <---<<< Not supposed to happen

  --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)