Re: Join sintax question

[Michael Stassen]

Douglas Sims wrote:

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...
You don't need LEFT JOIN, as just JOIN will do (see below).  Also, you 
can replace your list of OR conditions with one IN.  Like this

  SELECT DISTINCT AudioTrack.*
  FROM AudioTrack JOIN AudioTracks_Categories
  ON AudioTrack.AudioTrack_id = AudioTracks_Categories.AudioTrack_id
  WHERE AudioTracks_Categories.Category_id IN(val1,val2,...)
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
Hi Giulio

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} ...
Why LEFT JOINs?  I'm sure this works, but if an AudioTrack row has no 
corresponding AudioTracks_Categories row, the LEFT JOIN creates one with 
NULLS, which you then filter out with your WHERE clause.  It would be 
simpler (and more efficient) to do a plain join.

SELECT AudioTrack.*
FROM AudioTrack A
JOIN AudioTracks_Categories C1 ON A.AudioTrack_id=C1.AudioTrack_id
JOIN AudioTracks_Categories C2 ON A.AudioTrack_id=C2.AudioTrack_id
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 
than 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???
This won't work.  It returns one AudioTrack row for each 
Category.Category_id in the OR list.  Perhaps you meant to group by 
AudioTrack.AudioTrack_id?  Also, as above, you don't need a LEFT JOIN. 
Finally, you can use IN to simplify the list of OR conditions.  So, the 
OR query becomes this:

  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
Essentially, we've replaced DISTINCT with GROUP BY.  It's not clear to 
me which should be more efficient.

Now, consider that if you add COUNT(*) to the SELECT, you get an 
additional column containing the number of categories matched.  You can 
use this idea to write an alternate version of the AND version of the 
query, by adding a HAVING clause that requires that the number of 
categories matched equals the number of categories.

  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
For example, to find tracks with Category_id 1, 3, and 7, you would use

  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
This is nice for a couple of reasons.  First, I think it is easier to 
generalize to an arbitrary number of AND conditions than the JOIN for 
each condition version of AND.  Second, it offers flexibility.  For 
example, change the HAVING clause to

  HAVING COUNT(*) >= 2

to return all AudioTrack rows which match at least two of the specified 
categories.

Good luck.

-Doug
Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]