Changing a query to T-SQL to make search dynamic

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

SQL Server 2005 Forums

Transact-SQL (2005)

Changing a query to T-SQL to make search dynamic

Author

Topic

Firebrand
Starting Member

24 Posts

Posted - 2010-06-14 : 08:25:21

I’m currently constructing a website menu system from a database using a single table with multiple table joins and filters. The setup works well but is restrictive in terms of language.

At the moment I can only search for records based on a single language. What I’d like to be able to do is return individual records in an alternative language in the absence of my favoured language.
This is a simplified version of what I’m doing at the moment:

SELECT * FROM tblLanguages WHERE LanguageID = lang

I’d like to be able to do something like this (Excuse the syntax)

SELECT * FROM tblLanguages WHERE LanguageID = first_lang
IF (first_lang NO EXIST) THEN
SELECT * FROM tblLanguages WHERE LanguageID = alt_lang
END IF

So essentially each record is being checked to see if it returns content, and if it doesn’t then an additional search is made.

I’m pretty sure this can be done in Transact-SQL, I just need some suggestions about how to shape the Query in terms of syntax. Also, I’ve read that there are performance issues with such an approach, which is understandable, any advice on the subject would be appreciated.

Many thanks

saran_d28
Starting Member

36 Posts

Posted - 2010-06-14 : 08:34:59

if exists (SELECT * FROM tblLanguages WHERE LanguageID = first_lang)
SELECT * FROM tblLanguages WHERE LanguageID = first_lang
else
SELECT * FROM tblLanguages WHERE LanguageID = alt_lang

Firebrand
Starting Member

24 Posts

Posted - 2010-06-14 : 09:13:45

quote:
Originally posted by saran_d28

if exists (SELECT * FROM tblLanguages WHERE LanguageID = first_lang)
SELECT * FROM tblLanguages WHERE LanguageID = first_lang
else
SELECT * FROM tblLanguages WHERE LanguageID = alt_lang

Great, thanks very much.

So the Query you have posted above could return results like the following?

text here - first_lang
other suff - first_lang
some more - first_lang
extra stuff - alt_lang
content - first_lang
some stuff - alt_lang

Thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources