Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to use WHERE and CASE to filter 3 rows?

Author  Topic 

jchoudja
Starting Member

41 Posts

Posted - 2013-02-19 : 09:22:54
Hi, I want to join 3 tables and Use WHERE and CASE to filter and show a specific value.

Table
Member AS m
MemberGUID|MemberName
----------------------
1_________|_Thomas
2_________|_Sophia
3_________|_Martin
4_________|_Ashley
5_________|_Tom

MemberAttribute AS mat
MemberAttributeGUID|MemberGUID|AttributeGUID
--------------------------------------------
MA1________________|_1________|_A1
MA2________________|_2________|_A2
MA3________________|_1________|_A3
MA4________________|_4________|_A1
MA5________________|_4________|_A4
MA6________________|_1________|_A3

Attribute AS at
AttributeGUID|AttributeName
---------------------------
A1___________|_disable
A2___________|_Principal
A3___________|_Normal
A4___________|_Regular
A5___________|_student

Issue 1.
For All members with 'disable' attribute, Memberattribute will show 'Best'. For members with no or all other attribute,Memberattribute will show 'regular' Note same member might have 'disable' Attribute. In this case,Memberattribute will show 'Best'
So only one row per member


MemberGUID|MemberName|Memberattribute
-------------------------------------
1_________|_Thomas___|_Best
2_________|_Sophia___|_regular
3_________|_Martin___|_regular
4_________|_Ashley___|_Best
5_________|_Tom______|_regular

I haven't try anything because I have no idea on how to implement this
Thank you for your help


jc

jchoudja
Starting Member

41 Posts

Posted - 2013-02-19 : 10:20:03
Help Please. Let me know if you need more clarification.
All I need is to get the resul table no matter what method is used.
Thank you

jc
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-19 : 11:28:54
Can you try this?
SELECT
m.MemberGUID,
m.MemberName,
CASE WHEN s.MemberAttribute = 'Disable' THEN 'Best' ELSE 'Regular' END AS MemberAttribute
FROM
Member m
OUTER APPLY
(
SELECT TOP (1) AttributeName
FROM
Attribute a
INNER JOIN MemberAttribute ma
ON ma.attributeGuid = a.attributeGuid
WHERE
ma.MemberGuid = m.MemberGuid
ORDER BY
CASE WHEN AttributeName = 'Disable' THEN 0 ELSE 1 END
) s
Go to Top of Page
   

- Advertisement -