FULL-TEXT SEARCH over Two Tables with JOIN?
Posted by: Johan Ekström
Date: July 29, 2011 06:54AM
This is probably easier done, than said.
I got two tables, where I store information.
In one table (Artist) I only store (id,artist) columns.
In the other (Tracks) I store (id,artist,title,etc...) columns.
In both tables artist exists, but...
In (Artist) there is a actual name, where in (B) there is the id or id's if more than one, seperated by a "," (comma) .
So what I want is, when I do a SELECT or FULL-TEXT SEARCH, so it matches :
Artist.id with Tracks.artist.
Now this works (in some cases atleast) :
INNER JOIN artist
ON artist.id = tracks.artist
artist.artist LIKE "%artist%"
tracks.title LIKE "%title%";
Let's say artist contains two artists :
1 | Madonna
2 | Britney Spears
and tracks contains :
1 | 1 | Ray of Light
2 | 1 | Virgin
3 | 2 | Toxic
4 | 2 | Till The World Ends
5 | 1,2 | Me against The Music
The Above SELECT Query shows the correct RESULTS for the first 4 tunes,
if you search for Madonna or Britney, but not for the fifth tune,
only Madonna works on that since INNER JOIN is "ON artist.id = tracks.artist".
Is there anyway of perfecting this?!