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.
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 mMemberGUID|MemberName----------------------1_________|_Thomas2_________|_Sophia 3_________|_Martin4_________|_Ashley5_________|_TomMemberAttribute AS matMemberAttributeGUID|MemberGUID|AttributeGUID--------------------------------------------MA1________________|_1________|_A1MA2________________|_2________|_A2MA3________________|_1________|_A3MA4________________|_4________|_A1MA5________________|_4________|_A4MA6________________|_1________|_A3 Attribute AS atAttributeGUID|AttributeName---------------------------A1___________|_disable A2___________|_PrincipalA3___________|_NormalA4___________|_RegularA5___________|_studentIssue 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___|_Best2_________|_Sophia___|_regular 3_________|_Martin___|_regular4_________|_Ashley___|_Best5_________|_Tom______|_regularI haven't try anything because I have no idea on how to implement thisThank you for your helpjc |
|
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 youjc |
|
|
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 MemberAttributeFROM 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 |
|
|
|
|
|