SUB QUERY INSIDE CASE STATEMENT

• darunsurey

  Old Hand

  Points: 395

  More actions

  November 29, 2010 at 5:31 am

  #143739

  Hi Everyone,

  I need to know whether we can use corelated sub query in case satatements.

  My requiement is,

  I have a student table,employee table, Group and GroupMember table,

  Student and Employee table contains mobile numbers,name,address etc. of each student and employee.

  The Group table contains the Groupname,created date and groupmember table is a detail table of the group table, which contains member type(0 for students and 1 for employees),memberid(Employee or Student id) and groupid.

  I need to query the groupmembers name and table like below based on the member type.

  I request you to help me to get the records like below,

  GroupID Group Name Member Name Member Type Mobile No

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

  1 Group A Williams Employee xxxxxxxxxxx

  1 Group A Tom Student xxxxxxxxxxx

  Thanks in advance.

  Regards,

  Arun

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  November 29, 2010 at 5:45 am

  #1256189

  Having table scripts and sample data would help a lot.

  Take a look at the article linked in my signature and post the missing info.

  -- Gianluca Sartori

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  November 29, 2010 at 7:15 am

  #1256240

  darunsurey (11/29/2010)

  ---

  I need to know whether we can use corelated sub query in case satatements.

  Arun

  Yes you can but it's rarely a good idea.

  darunsurey (11/29/2010)

  ---

  I have a student table, employee table, Group and GroupMember table

  Can we have them too, please?

  CREATE TABLE....

  INSERT INTO....

  ^{“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

• Grant Fritchey

  SSC Guru

  Points: 399278

  More actions

  November 29, 2010 at 7:43 am

  #1256258

  Without the code and structures it's hard to know, but based on what you described, it sure sounds like all you need is a simple join to bring back the appropriate data. I would go with that approach. Any time you start creating all kinds of complex structures within your queries, you're probably starting to dig a hole. At that point, step back, reassess the situation, talk to a peer or post a question here for feedback.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning