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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 filter but also get the ones not associated		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

magmo
Constraint Violating Yak Guru

465 Posts
Posted - 03/12/2013 :  06:45:00  Show Profile  Reply with Quote
Hi

I have the following query


SELECT DISTINCT TOP (100) PERCENT dbo.tbl_WebbLanguage.LanguageName, dbo.tbl_WebbLanguage.LangID
FROM         dbo.tbl_LanguageAccess INNER JOIN
                      dbo.tbl_WebbLanguage ON dbo.tbl_LanguageAccess.LangID = dbo.tbl_WebbLanguage.LangID
ORDER BY dbo.tbl_WebbLanguage.LanguageName



This give me all the distinct language names which is fine, but in dbo.tbl_LanguageAccess there is also a column called "LoginID" which I would like to use, so if I filter on Login = 3 I should return all the languages that are associated with that LoginID, but also the ones that are not, the ones that are associated should be marked as "InUse", I tried to add this...


SELECT DISTINCT TOP (100) PERCENT dbo.tbl_WebbLanguage.LanguageName, dbo.tbl_WebbLanguage.LangID
FROM         dbo.tbl_LanguageAccess INNER JOIN
                      dbo.tbl_WebbLanguage ON dbo.tbl_LanguageAccess.LangID = dbo.tbl_WebbLanguage.LangID
WHERE     (dbo.tbl_LanguageAccess.LoginID = 1) OR
                      (dbo.tbl_LanguageAccess.LoginID IS NULL)
ORDER BY dbo.tbl_WebbLanguage.LanguageName



But that doesn't give me any rows at all.

visakh16
Very Important crosS Applying yaK Herder

India
47189 Posts
Posted - 03/12/2013 :  06:48:31  Show Profile  Reply with Quote 

SELECT DISTINCT TOP (100) PERCENT dbo.tbl_WebbLanguage.LanguageName, dbo.tbl_WebbLanguage.LangID,
CASE WHEN dbo.tbl_LanguageAccess.LoginID = 1 THEN 'InUse' END
FROM         dbo.tbl_LanguageAccess INNER JOIN
                      dbo.tbl_WebbLanguage ON dbo.tbl_LanguageAccess.LangID = dbo.tbl_WebbLanguage.LangID

ORDER BY dbo.tbl_WebbLanguage.LanguageName


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

magmo
Constraint Violating Yak Guru

465 Posts
Posted - 03/12/2013 :  06:55:34  Show Profile  Reply with Quote
Thanks for a fast reply, one thing though. If there is associated languages I get 2 results for each languagename, one where "InUse" is true and one where "InUse" is null..
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
1451 Posts
Posted - 03/12/2013 :  07:24:29  Show Profile  Reply with Quote
SELECT DISTINCT TOP (100) PERCENT dbo.tbl_WebbLanguage.LanguageName, dbo.tbl_WebbLanguage.LangID,
MAX(CASE WHEN dbo.tbl_LanguageAccess.LoginID = 1 THEN 'InUse' END) OVER(PARTITION BY dbo.tbl_WebbLanguage.LangID)
FROM ..........


--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47189 Posts
Posted - 03/12/2013 :  07:52:59  Show Profile  Reply with Quote
quote:
Originally posted by magmo

Thanks for a fast reply, one thing though. If there is associated languages I get 2 results for each languagename, one where "InUse" is true and one where "InUse" is null..

what do you mean by associated languages. show some sample data to illustrate your issue

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

magmo
Constraint Violating Yak Guru

465 Posts
Posted - 03/12/2013 :  08:18:00  Show Profile  Reply with Quote
quote:
Originally posted by bandi

SELECT DISTINCT TOP (100) PERCENT dbo.tbl_WebbLanguage.LanguageName, dbo.tbl_WebbLanguage.LangID,
MAX(CASE WHEN dbo.tbl_LanguageAccess.LoginID = 1 THEN 'InUse' END) OVER(PARTITION BY dbo.tbl_WebbLanguage.LangID)
FROM ..........


--
Chandu



Excellent, Thank you very much!
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
1451 Posts
Posted - 03/12/2013 :  08:18:43  Show Profile  Reply with Quote
quote:
Originally posted by magmo

quote:
Originally posted by bandi

SELECT DISTINCT TOP (100) PERCENT dbo.tbl_WebbLanguage.LanguageName, dbo.tbl_WebbLanguage.LangID,
MAX(CASE WHEN dbo.tbl_LanguageAccess.LoginID = 1 THEN 'InUse' END) OVER(PARTITION BY dbo.tbl_WebbLanguage.LangID)
FROM ..........
--
Chandu
Excellent, Thank you very much!

Welcome

--
Chandu
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.06 seconds. Powered By: Snitz Forums 2000