I would first store your original query's results in a temp table:
CREATE TEMPORARY TABLE tmpTracks
SELECT AudioTrack.*
FROM AudioTrack
INNER JOIN AudioTracks_Categories
ON AudioTrack.AudioTrack_id = AudioTracks_Categories.AudioTrack_id
WHERE AudioTracks_Categories.Category_id IN (cat1,cat2,...)
GROUP BY AudioTrack.AudioTrack_id
HAVING COUNT(*) = number_of_categories_to_match
Then join to the temp table to build the full list of categories to get
all of the categories for those tracks that were in all of the specific
categories you declared in the first query.
SELECT tt.*, atc.Category_Id
FROM tmpTracks tt
INNER JOIN AudioTracks_Categories atc
ON atc.AudioTrack_ID = tt.AudioTrack_Id;
DROP TABLE tmpTracks;
The only other option would be to use a sub-select/sub-query which you may
or may not be able to execute.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Giulio <[EMAIL PROTECTED]> wrote on 09/20/2004 03:13:28 PM:
> 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.
>
> to find all tracks belonging to a given list of categories, the query I
> use is ( this was another tip from this list ):
>
> SELECT AudioTrack.*
> FROM AudioTrack JOIN AudioTracks_Categories
> ON AudioTrack.AudioTrack_id = AudioTracks_Categories.AudioTrack_id
> WHERE AudioTracks_Categories.Category_id IN (cat1,cat2,...)
> GROUP BY AudioTrack.AudioTrack_id
> HAVING COUNT(*) = number_of_categories_to_match
>
> my problem now is, how can i find the data tracks belonging to a given
> list of categories, and retrieve on the same query ALL the categories
> they belong to?
>
> I mean, using:
>
> SELECT AudioTrack.*
> FROM AudioTrack JOIN AudioTracks_Categories
> ON AudioTrack.AudioTrack_id = AudioTracks_Categories.AudioTrack_id
> WHERE AudioTracks_Categories.Category_id IN (1,3,7)
> GROUP BY AudioTrack.AudioTrack_id
> HAVING COUNT(*) = 3
>
> I know that all the tracks retrieved on the query belong to categories
> 1,3 and 7, but some of them could belong to more categories not
> specified in the query, and I would like to have also them in the
> result...
>
> Hope this was clear...
>
> thank you
>
> Giulio
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
