MySQL Forums
Forum List  »  Full-Text Search

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) :


SELECT
artist.artist,
tracks.title,
tracks.episode
FROM music.tracks
INNER JOIN artist
ON artist.id = tracks.artist
WHERE
artist.artist LIKE "%artist%"
AND
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?!

Options: ReplyQuote


Subject
Views
Written By
Posted
FULL-TEXT SEARCH over Two Tables with JOIN?
14197
July 29, 2011 06:54AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.