Skip navigation links

MySQL Forums :: Full-Text Search :: FULL-TEXT SEARCH over Two Tables with JOIN?


Advanced 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? 5579 Johan Ekström 07/29/2011 06:54AM
Re: FULL-TEXT SEARCH over Two Tables with JOIN? 2072 Rick James 08/07/2011 12:33PM
Re: FULL-TEXT SEARCH over Two Tables with JOIN? 1811 Johan Ekström 08/07/2011 04:27PM
Re: FULL-TEXT SEARCH over Two Tables with JOIN? 1886 Rick James 08/07/2011 11:16PM


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.