Hi Giulio
Hi Doug,
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} ...
It works like a charm. Brilliant! I was far away from such a solution. very appreciated help.
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.
OK, I'll add them
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???
This doesn't seem to work for me. it seems that the GROUP BY is applyed at the end of the other operations, and I've returned only one audiotrack record for every category specified on the WHERE clauses.
Good luck.
-Doug
Best regards and thanks again for your help,
Giulio
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
