Help with a Query - SQL Server Forums

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

Author

Topic

Pedro Teixeira
Starting Member

5 Posts

Posted - 2010-05-06 : 04:53:07

Hello there. I'm having troubles with a query to return multiple keywords of the same table. I'll explain in code.

This are my 3 tables: ( Picture is in a N-N relationship with Keywords)

Picture PicKey Keywords
ID_Pic Pic ID_PK ID_Pic ID_Key ID_Key Keyword
1 LA.jpg 1 2 2 1 moon
2 NY.jpg 2 2 3 2 sun
3 SE.jpg 3 2 4 3 beach
4 MI.jpg 4 1 1 4 hot
5 DA.jpg 5 3 1 5 cold

I want To get the Picture by searching multiple keywords, do I make myself clear?
I know how to code for one single keyword :

SELECT P.ID_Pic, P.Pic FROM Picture P, Keywords K, PicKey PK WHERE P.Pic_ID=PK.Pic_ID AND PK.ID_Key=K.ID_Key AND K.Keyword = "moon"

And to multiple keywords if I add more conditions like this:

SELECT P.ID_Pic, P.Pic FROM Picture P, Keywords K, PicKey PK WHERE P.Pic_ID=PK.Pic_ID AND PK.ID_Key=K.ID_Key AND K.Keyword = "moon" AND K.Keyword= "beach" AND K.Keyword= "sun"

My problem is this keywords are values retrieved by a POST from php. So how can I make this work?

Basically it's a form where people insert various keywords, and the database shows them just the right one according to the keywords.

Please help me with this.

Thank you in advance

apodemus
Starting Member

30 Posts

Posted - 2010-05-06 : 04:57:09

what about IN clause? .....AND K.Keyword IN ('beach','moon','sun') ?

apodemus

Pedro Teixeira
Starting Member

5 Posts

Posted - 2010-05-06 : 05:02:01

Omg that easy! I wasn't aware of that. Thank you very much :)

Pedro Teixeira
Starting Member

5 Posts

Posted - 2010-05-06 : 05:23:42

Just one last question about this. That IN clause returns the pictures with those EXACT keywords, or the ones that contain some of those keywords?

apodemus
Starting Member

30 Posts

Posted - 2010-05-06 : 05:25:46

by the way......AND K.Keyword = "moon" AND K.Keyword= "beach" AND K.Keyword= "sun" can't work :), "sun" = "beach" is always false, for SQL Server of course, not in life :)

apodemus

apodemus
Starting Member

30 Posts

Posted - 2010-05-06 : 05:27:32

k.keyword must be one of values in ()

apodemus

apodemus
Starting Member

30 Posts

Posted - 2010-05-06 : 05:31:52

if you want to find keyword thatcontains some string, you should use LIKE clause, k.keyword like '%sun%' return all records with keyword contains SUN, like sunshine, sunny etc.

apodemus

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources