Help with a Query ( IN Operator )

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

Help with a Query ( IN Operator )

Author

Topic

Pedro Teixeira
Starting Member

5 Posts

Posted - 2010-05-22 : 04:45:11

Hello there, I have this query that goes like this:


SELECT SP.ServicePic_ID, SP.ServicePic FROM Service_Pic SP, Keyword K, SPKeywords SPK, Date D WHERE SP.ServicePic_ID=SPK.ServicePic_ID AND SPK.Keyword_ID=K.Keyword_ID AND SP.Date_ID=D.Date_ID AND D.Date BETWEEN '1960-01-01' AND '2000-01-01' AND K.Keyword IN ('beach','sun')

It works just fine! Until the point I don't introduce any keywords.. for example


SELECT SP.ServicePic_ID, SP.ServicePic FROM Service_Pic SP, Keyword K, SPKeywords SPK, Date D WHERE SP.ServicePic_ID=SPK.ServicePic_ID AND SPK.Keyword_ID=K.Keyword_ID AND SP.Date_ID=D.Date_ID AND D.Date BETWEEN '1960-01-01' AND '2000-01-01' AND K.Keyword IN ('','')

Is it possible to use this IN statement but the result of the query not to be dependent of all the values to be right?

I mean, in my particular case, I wan't the query to return this : SP.ServicePic_ID, SP.ServicePic ... whether some keywords are specified or not... or return this SP.ServicePic_ID, SP.ServicePic again whether some dates are specified or not..

Do I make myself clear?

Having a table of Dates and a Table of Keywords, if the user searches just for keywords it would show the pictures filtered by thoose keywords, if only the date, the pictures filtered by that date, or both, the pictures filtered by date and keywords..

Can someone please help me to modidy the WHERE clause of my query so this can happen? Thank you very very much, If you don't understand I'll try to rephrase it . thanks

Pedro Teixeira
Starting Member

5 Posts

Posted - 2010-05-22 : 05:09:00

Basiclly I want to be able to retrieve the dates even if the query doesnt have any keywords, something like:

SELECT SP.ServicePic_ID, SP.ServicePic FROM Service_Pic SP, Keyword K, SPKeywords SPK, Date D WHERE SP.ServicePic_ID=SPK.ServicePic_ID AND SPK.Keyword_ID=K.Keyword_ID AND SP.Date_ID=D.Date_ID AND D.Date BETWEEN '1960-01-01' AND '2000-01-01' AND K.Keyword IN ('','')

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-22 : 06:15:29

I am not much clear on your requirement.

Just give a try to below select statement whether this is the solution you were looking for :

SELECT SP.ServicePic_ID, SP.ServicePic FROM Service_Pic SP, Keyword K, SPKeywords SPK,
Date D WHERE (SP.ServicePic_ID=SPK.ServicePic_ID
AND SPK.Keyword_ID=K.Keyword_ID AND SP.Date_ID=D.Date_ID
AND D.Date BETWEEN '1960-01-01' AND '2000-01-01') or( K.Keyword IN ('',''))

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources