Search box - using WHERE and LIKE together

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

Search box - using WHERE and LIKE together

Author

Topic

Nitesh9999
Starting Member

5 Posts

Posted - 2006-02-01 : 13:02:46

Hi im creating a music site (for uni) and I need a search box.

I have managed to put together

strSQL = "select * from ec_tunes where tune_name like '%" & strsearchboxinput & "' or tune_name like '" & strsearchboxinput & "%' or tune_name like '%" & strsearchboxinput & "%'"

That works well for searching for the songs as it uses the querystring from the search box page.

But I need to search over 4 tables (Tune, Artist, Label and Format). I have managed to join these tables like below

SELECT artist_name, label_name, tune_name, format_name
FROM ec_tunes, ec_labels, ec_artists, ec_tunes_artists, ec_formats
WHERE ec_labels.label_id = ec_tunes.label_id 
AND ec_artists.artist_id = ec_tunes_artists.artist_id
AND ec_tunes_artists.tune_id = ec_tunes.tune_id
AND ec_formats.format_id = ec_tunes.format_id

This brings back all the songs in the database and shows data from 4the 4 different tables.

How can I combine the 2, so that i can use the query string and the LIKE operator to search over the 4 tables?

Any help appreciated.

bakerjon
Posting Yak Master

145 Posts

Posted - 2006-02-01 : 16:05:13

Well, first of all the LIKE's are redundant. If you use LIKE '%string%', it will find anything with that string even if it starts/ends with that string. But, it's not a good idea to use that if your dataset is larger.

Next try something like
"SELECT artist_name, label_name, tune_name, format_name
FROM

ec_tunes INNER JOIN
ec_labels on ec_labels.label_id = ec_tunes.label_id
INNER JOIN ec_artists on ec_artists.artist_id = ec_tunes_artists.artist_id
INNER JOIN ec_tunes_artists on ec_tunes_artists.tune_id = ec_tunes.tune_id
INNER JOIN ec_formats on ec_formats.format_id = ec_tunes.format_id
WHERE
tune_name like '%" & strsearchboxinput & "%'"

See if that gives you what you are looking for

Jon
-Like a kidney stone, this too shall pass.

http://www.sqljunkies.com/weblog/outerjoin

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2006-02-01 : 17:17:58

I maybe reading this wrong but I think unions operators instead of joins are needed.

SELECT artist_name, label_name, tune_name, format_name
FROM ec_tunes, ec_labels, ec_artists, ec_tunes_artists, ec_formats
WHERE ec_labels.label_id = ec_tunes.label_id
AND ec_artists.artist_id = ec_tunes_artists.artist_id
AND ec_tunes_artists.tune_id = ec_tunes.tune_id
AND ec_formats.format_id = ec_tunes.format_id

should be...

select tune_id as ResultID, tune_name as ResultName from ec_tunes
where tune_name like '%whatever%'
union all
select artist_id, artist_name from ec_artists
where artist_name like '%whatever%'
union all
select format_id, format_name from ec_formats
where format_name like '%whatever%'

ec_tunes_artists looks like a junction table so it should be left out as the search against the idividual tunes and artists tables would make the junction table search redundant. I also renamed the restults as ResultID and ResultName for clarity's sake (so Poison the band wouldn't be interpretted as Poison the song). So if a search is done for %Poison% the results would give back the Alice Cooper song Poison from the ec_tunes table or songs from the band Poison from the ec_artists table.

From there depending on what kind of record key was returned would determine how the data was joined back to get the resulting data from the 4 tables.

m2c,
Justin

"Hit me with a shovel 'cause I can't believe I dug you."

Nitesh9999
Starting Member

5 Posts

Posted - 2006-02-02 : 09:07:29

Thanks

I managed to get round the problem by creating a view. This made it easy to use like.

Thanks for all the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources