SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to use WHERE and CASE to filter 3 rows?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jchoudja
Starting Member

USA
41 Posts

Posted - 02/19/2013 :  09:22:54  Show Profile  Reply with Quote
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

USA
41 Posts

Posted - 02/19/2013 :  10:20:03  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 02/19/2013 :  11:28:54  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000