search a column in one table and multiple records

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)

search a column in one table and multiple records

Author

Topic

monaya
Yak Posting Veteran

58 Posts

Posted - 2009-03-12 : 12:31:44

[code]booktags
booktagid tag bookid
10 parent 32
11 romance 52
16 love 52[/code]

[code]booknames
bookid bookname
32 how fathers handle children
52 A man and a woman
43 The cat that crossed the street
[/code]
I have a textfield that needs to search the book name as well as tags and return the book id and name. I cant figure it out since the relationship between the tables is one to many.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-12 : 12:41:32

so what should be your expected output from above?

monaya
Yak Posting Veteran

58 Posts

Posted - 2009-03-12 : 13:52:24

quote:
Originally posted by visakh16

so what should be your expected output from above?

A search term is entered in the textfield and a match is checked in all records of the tag column of the booktags table as well as all the records of the bookname column in the booknames table. I want the bookname returned in all cistumstances.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-12 : 14:02:16

[code]
SELECT bookname
FROM booknames bn
JOIN booktags bt
ON bt.bookid=bn.bookid
WHERE bt.tag = @searchterm
OR bn.bookname LIKE '%'+ @searchterm + '%'
[/code]

monaya
Yak Posting Veteran

58 Posts

Posted - 2009-03-12 : 14:10:37

quote:

Originally posted by visakh16


SELECT bookname
FROM booknames bn
JOIN booktags bt
ON bt.bookid=bn.bookid
WHERE bt.tag = @searchterm
OR bn.bookname LIKE '%'+ @searchterm + '%'

I tried that, but what happens if you search for "man" the bookname "A man and a woman" comes up three times since you're joining by bookid and it has two tags too.

You'll get:

A man and a woman
A man and a woman
A man and a woman

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-12 : 14:13:52

then use DISTINCT

SELECT DISTINCT bookname
FROM booknames bn
JOIN booktags bt
ON bt.bookid=bn.bookid
WHERE bt.tag = @searchterm
OR bn.bookname LIKE '%'+ @searchterm + '%'

monaya
Yak Posting Veteran

58 Posts

Posted - 2009-03-12 : 14:17:59

yes, I just realized that. Thanks!

How come if you search for a word that resides somewhere in the title, nothing comes up? like "man" will not return the book "A man and a woman"
I tried LIKE '%'+ @searchterm + '%' I still get nothing.

monaya
Yak Posting Veteran

58 Posts

Posted - 2009-03-12 : 14:27:02

Oh no theres a problem with the SQL query. It does search all columns but I noticed its only returning searches that have tags stored in the booktags table. if there are no tags, the book name does not come up

monaya
Yak Posting Veteran

58 Posts

Posted - 2009-03-12 : 14:27:23

Changing JOIN to LEFT JOIN worked.

monaya
Yak Posting Veteran

58 Posts

Posted - 2009-03-12 : 14:28:46

I'm curious what do you have to do to get woman to come up if someone enters women? I hear its complicated.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-13 : 06:01:38

quote:
Originally posted by monaya

I'm curious what do you have to do to get woman to come up if someone enters women? I hear its complicated.

Use soundex function

select soundex('woman'),soundex('women')

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources