I think you could do this by repeatedly left-joining the categories table as in this:
SELECT AudioTrack.*
FROM AudioTrack A
LEFT JOIN AudioTracks_Categories C1 ON A.AudioTrack_id=C1.AudioTrack_id
LEFT JOIN AudioTracks_Categories C2 ON A.AudioTrack_id=C2.AudioTrack_id
LEFT JOIN AudioTracks_Categories C3 ON A.AudioTrack_id=C3.AudioTrack_id
...
WHERE C1.Category_id={first category} AND C2.Category_id={second category} AND C3.Category_id={third category} ...
If you have indexes for the Audio_Tracks_Categories.Category_id column and the AudioTrack.AudioTrack_id column, this should be more efficient that you might think.
Also, I think a slightly more efficient way of doing the first query you sent, the "OR" query, would be to GROUP BY the AudioTracks_Categories, as this way it will have to join fewer rows from the AudioTrack table, as this:
select AudioTrack.* from AudioTrack left join AudioTracks_ Categories on AudioTrack.AudioTrack_id = AudioTracks_ Categories.AudioTrack_id where AudioTracks_ Categories.Category_id = somevalue or AudioTracks_ Categories.Category_id = somevalue... GROUP BY Categories.Category_id
Does anyone else agree or disagree with this idea???
Good luck.
-Doug
Giulio wrote:
HI all,
I have two tables, let's say AudioTrack and Category
Every AudioTrack record can belong to one or more ( or none ) Categories.
I have created an intermediate table, AudioTracks_ Categories containing only the IDs of AudioTrack and Category to keep track of the link.
I can easily find AudioTrack records for a given Category id:
select AudioTrack.* from AudioTrack left join AudioTracks_ Categories on AudioTrack.AudioTrack_id = AudioTracks_ Categories.AudioTrack_id where AudioTracks_ Categories.Category_id = somevalue
I can also easily find AudioTrack records for a given Category id OR others Category id, adding distinct to avoid row duplication for AudioTrack records contained in more than one of the specyfied categories:
select distinct AudioTrack.* from AudioTrack left join AudioTracks_ Categories on AudioTrack.AudioTrack_id = AudioTracks_ Categories.AudioTrack_id where AudioTracks_ Categories.Category_id = somevalue or AudioTracks_ Categories.Category_id = somevalue...
Now the question is, how can I handle an AND query, I mean how can I have returned only the AudioTrack records that belongs to ALL the Category IDs I put in the query?
Hope the question was clear... :)
thanks in advance for your answer,
Giulio
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
