ASP.NET - sql query - Asked By msakt on 14-Jun-12 12:49 AM

i have two table

groupcustomermanagement table have
GID   BusinessId   GroupName  active

1      9          aaa       1
2      9          bbbb     0
3      9           cccc     1
4       9          dddd    0

SelectMemberId table have

Busid   MemberID   GroupID

9         1        1
9          2       2
9          1        3


i want sp...if  give memberid and businessid
  for example  memberid=1 and businessid =9 is pass
output is 
Gid   groupname GroupSelect
1      aaaa       true
2      bbb        false
3      cccc        true
4      dddd       false






TSN ... replied to msakt on 14-Jun-12 01:15 AM

here is the store procedure where I am using the Case Statement in order to show true false values

create procedure MYPROC

@BID int

As

SELECT Gid , groupname ,

(CASE active

WHEN 1 THEN 'true'

WHEN 0 THEN 'false'

ELSE 'Other'

END) as GroupSelect from Table1 where BusinessId =@BID

 

Chintan Vaghela replied to msakt on 14-Jun-12 01:28 AM

Hi Frndz,

 

Functionality: Query

 

 

Need to use SUbquery for that find true /false status

 

Select COUNT(*) from Table12 as SelectMemberId 

              where memberid =and SelectMemberId .businessid=and SelectMemberId.groupid = groupcustomermanagement.GID

 

 

If Count 0 then false

Else true

 

Full Query:

 

select

    GID,

    GroupName,

    CASE WHEN (

              Select COUNT(*) from Table12 as SelectMemberId 

              where memberid =and SelectMemberId .businessid=and SelectMemberId.groupid = groupcustomermanagement.GID) = 0

        then 'false'

        else 'true'

        end as GroupStatus

 from Table2 as groupcustomermanagement

where groupcustomermanagement.BusinessID = 9

Hope this helpful!

Thanks