LinuxQuestions.org
LinuxAnswers - the LQ Linux tutorial section.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices



Reply
 
Search this Thread
Old 07-19-2011, 01:42 PM   #1
krilen
Member
 
Registered: Mar 2004
Distribution: CentOS, FreeBSD
Posts: 42

Rep: Reputation: 15
SQL query: multiple combinations


Hi.

Need help with a SQL query.

I need to be able to search after multiple words and get the unique text that the words belong to.

To simplify it for you, I have two tables:

text
--------------
text.id
text.word_id

word
--------------
word.id
word.word (dog, bird, frog....)

Now I have a couple of texts:
-----------------------------------------------
Text 1: "dog frog bird" (gets text.id = 1)
Text 2: "dog cat mouse" (gets text.id = 2)
Text 3: "cow dog bird" (gets text.id = 3)
-----------------------------------------------

I now split up the text into the different words:
-----------------------------------------------
dog gets (word.id = 1, text.id = 1)
frog gets (word.id = 2, text.id = 1)
bird gets (word.id = 3, text.id = 1)

dog gets (word.id = 1 [since dog already exits], text.id = 2)
cat gets (word.id = 4, text.id = 2)
mouse gets (word.id = 5, text.id = 2)

cow gets (word.id = 6, text.id = 3)
dog gets (word.id = 1 [since dog already exits], text.id = 3)
bird gets (word.id = 3 [since bird already exists], text.id = 3)
-----------------------------------------------

Now I want to make a search

If I search for 'cat' I get text.id = 2
If I search for 'dog' I get text.id = 1,2,3
If I search for 'bird' I get text.id = 1,3

and so on.

But I want a SQL query that lets me search for multiple animal at one go

If I search for 'cat' AND 'dog' I get text.id = 2
If I search for 'dog' AND 'bird' I get text.id = 1,3

and so on.

Also you must be able to search after multiple word, dog, cat, wolf, pig and so on at one go.

The query that I used that failed was:
-----------------------------------------------
SELECT DISTINCT text.id FROM text
LEFT JOIN word ON (word.id = text.word_id)
WHERE word.word LIKE '%dog%' AND word.word LIKE '%bird%'
-----------------------------------------------

I have tried diffrent JOINS but no luck, what must I do, a select in a select, a new table... I'am running out of ideas.

I use LIKE because you may want to search after 'hippo' and stil get 'hippopotamus'.

Thanks in advanced

/krister
 
Old 07-19-2011, 08:37 PM   #2
graemef
Senior Member
 
Registered: Nov 2005
Location: Hanoi
Distribution: Fedora 13, Ubuntu 10.04
Posts: 2,379

Rep: Reputation: 148Reputation: 148
try an inner join rather then a left join.
 
Old 07-21-2011, 01:41 PM   #3
krilen
Member
 
Registered: Mar 2004
Distribution: CentOS, FreeBSD
Posts: 42

Original Poster
Rep: Reputation: 15
Sorry don't work.

When I run this SQL query:
-------------------------------------------------------
SELECT DISTINCT a_text.id FROM a_text
INNER JOIN a_word ON (a_word.id = a_text.word_id)
WHERE a_word.word LIKE '%dog%' AND a_word.word LIKE '%bird%'
-------------------------------------------------------

(I had to rename my tables to a_**** so they don't effect anything else )

I have these values in my tables

a_text: (id, word_id)
-----------------------
1, 1
1, 2
1, 3
2, 1
2, 4
2, 5
3, 1
3, 3
3, 6

a_word: (id, word)
-----------------------
1, dog
2, frog
3, bird
4, cat
5, mouse
6, cow

So if I search for 'frog' I get -> 1 (ok), 'dog' - > 1,2,3 (ok)
But that is just with one 'LIKE' (WHERE a_word.word LIKE '%dog%')

I want to be able to search with multiple 'LIKE', 'dog' and 'bird' should get me -> 1,3, now it gets me nothing

Anyone got any ideas?

/Krister
 
Old 07-21-2011, 07:50 PM   #4
graemef
Senior Member
 
Registered: Nov 2005
Location: Hanoi
Distribution: Fedora 13, Ubuntu 10.04
Posts: 2,379

Rep: Reputation: 148Reputation: 148
For multiple searches you will be looking for dog OR bird. Not dog AND bird
 
Old 07-21-2011, 08:33 PM   #5
bgeddy
Senior Member
 
Registered: Sep 2006
Location: Liverpool - England
Distribution: slackware64 13.37 and -current, Dragonfly BSD
Posts: 1,810

Rep: Reputation: 227Reputation: 227Reputation: 227
Quote:
Also you must be able to search after multiple word, dog, cat, wolf, pig and so on at one go.
If by this you mean to return the a_text.id which has all the searched members in it's records, e.g. given your example searching for "dog","cat","mouse" would return a_text.id=2 then this should do it - you'll need to adjust the having term for the number of members you are searching for when it's not the exampled three:
Code:
select id from a_text where word_id in (select id from a_word where word in ("dog","cat","mouse")) group by id having count(distinct word_id)=3;
You could wrap that up to count the number of members you are searching for and drop the result in your query to save tailoring it each time. So as it stands a search for "dog" and "bird" would be done like this - note the adjusted "having count(distinct word_id)=2" as you are now searching for two terms not three:
Code:
select id from a_text where word_id in (select id from a_word where word in ("dog","bird")) group by id having count(distinct word_id)=2;
returning id's of 1 and 3 as these text entries exist with both "dog" and "bird".

Last edited by bgeddy; 07-22-2011 at 04:21 AM.
 
1 members found this post helpful.
Old 07-22-2011, 01:45 PM   #6
krilen
Member
 
Registered: Mar 2004
Distribution: CentOS, FreeBSD
Posts: 42

Original Poster
Rep: Reputation: 15
Thanks bgeddy.

That works better then my SQL query and it moves me forward towards the end.

Now I need to be able to use LIKE in the query since you should be able to search for "hippo" and still get "hippopotamus".

I think I only need to add my 'LIKE' query where your select word query is added.

/Krister
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
sql query aocferreira Programming 2 05-13-2011 04:58 AM
sql query help struct Programming 2 10-27-2010 01:29 AM
SQL: do math in query Ephracis Programming 4 04-25-2009 03:47 AM
SQL query help pls. vickr1z Programming 8 10-19-2004 12:25 AM


All times are GMT -5. The time now is 05:11 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration