FreeTextTable Query not working

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 2008 Forums

Transact-SQL (2008)

FreeTextTable Query not working

Author

Topic

gurjit
Starting Member

7 Posts

Posted - 2011-04-05 : 09:27:48

Hello friends,

i've two tables

there are two foriegn keys in the Songs table (ArtistID and ArtistIDSecond) both referring to the Primary Key (ArtistID) in the Artists table

, i've written the following FreeTextTable Select query, to retrieve both the values from ArtistID and ArtistIDSecond, it's not working, pls help

create proc [dbo].[FullTextArtistSearch]
@SearchWord varchar(50)
AS
select s.song, s.songid, s.lyrics, a.fname as [First], a2.fname as [Second] , a.artistid, 
a.ArtistImageUrl from dbo.Artists a
inner join FREETEXTTABLE(dbo.Artists, fname, @SearchWord) as Key_TBL_Artist
on a.ArtistID = key_TBL_Artist.[KEY]
inner join dbo.Songs s 
on a.ArtistID = s.ArtistIDSecond
inner join dbo.Artists a2
on a2.ArtistID = s.ArtistID

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-05 : 11:42:38

what if you have more than one artist on a song? what if you have 15 artists on a song such as compilations , etc?

1. On Songs table remove ArtistID and ArtistIDSecond
2. you need an intersection table SongArtist
SongArtist
ArtistID int
SongID int

Make sure to create FK Constraints on ArtistID and SongID . Make those two fields Unique primary key

Who is your data source?

If you don't have the passion to help people, you have no passion

gurjit
Starting Member

7 Posts

Posted - 2011-04-05 : 12:30:35

thanks for your reply Yosiaz,

actually these are my 3 tables, Albums, Artists and Songs, I agree a song can sure have multiple artists, that's why i created Two Columns for two artists like ArtistID and ArtistIDSecond in the Songs table, correct me if i'm wrong, you think i need to create a 4th table SongArtist ?

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-05 : 13:29:31

Hey gurjit

you say "I agree a song can sure have multiple artists" but your design allows only for two artists unless you are going to stuff other artists in ArtistID?

So yes I thing you should change your design in my opinion! who is your music data provider?

If you don't have the passion to help people, you have no passion

gurjit
Starting Member

7 Posts

Posted - 2011-04-05 : 14:30:57

hey Yosiaz,

i agree with you for this many to many relationship, i'd require an intersection table for a one-to-many kinda relationship to be created thanks a ton, you asked about my songs provider, it's none i'm gonna host it in india and use hindi songs early on and later on i think i'll tie up for a webservice with napster or raphsody

thanks again for all ur time and effort my friend

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-05 : 14:59:37

glad to help. we are working on a music project and it is very fascinating. a good design up front will save you tons of work later, like anything else. Remember also there is a difference between music composer, music performer. Mit Jaaye Gham by dum Maaro Dum could be performed by Tees Maar Khan's album. The same artist might not have written or composed the song. also not all songs have albums. a song could be released as just a song on the internet with no album. you have to think all this through.

If you don't have the passion to help people, you have no passion

gurjit
Starting Member

7 Posts

Posted - 2011-04-05 : 15:58:52

yeah, i do agree that a good design would save a lot of effort later on coz, once millions of songs have been added, changing the design would be scary, earlier i had included only one artist, later on i figured out i need more for duet and compilations, then my freetexttable wasn't working, so i figured out with ur help that many-to-many mistake i made, i've created SongArtist table now and would try to figure out much on that note, what kinda music project are you guys working on , is it a website and r u indian since you spoke about Tees maar khan and stuff

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-05 : 16:12:16

no I am not Indian but you know you can google, schmoogle anything nowadays :) we are doing exactly what you are doing but not limited for type of songs. do you have different genres of Hindi music? rap, rock, country, jazz, hip/hop/ you need a table like that
Classical · Bhajan · Ghazal · Qawwali · Sufi Folk · Filmi · Pop · Rock · Hip Hop

Hindustani music, Carnatic music

regions

Andaman and Nicobar Islands
Andhra Pradesh · Arunachal Pradesh · Assam
Bihar · Chhattisgarh · Goa · Gujarat · Haryana
Himachal Pradesh · Jammu & Kashmir · Jharkhand
Karnataka · Kerala
Madhya Pradesh · Maharashtra · Manipur
Meghalaya · Mizoram · Nagaland · Orissa
Punjab · Rajasthan · Sikkim · Tamil Nadu
Tripura · Uttar Pradesh · Uttaranchal
West Bengal

If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources