Try this... select a.ID from products a, categories b where a.CategoryID=b.ID and (b.ID='21' OR b.ParentID='21')
The query is pretty straigthforward and I believe it's quite easy to understand. Hope this is what you wanted! A tip... only use left and right joins whenever you want what is on the "left" or "right" side of the join in your results, even if you can't get any matches on the "opposite" side. For example (I'll try to make it simple!): imagine you have a Video Club.You have movies, and you have loans. Normally, movies in one table (the left table in this example) and loans in different table (the right table). Now, imagine you would like to print a list of ALL movies in your database, and ALSO list all loans for each movie. You could do a LEFT JOIN similar to this: select movies.*, loans.* from movies left join loans on (movies.id = loans.movieid) order by movies.title, loans.loan_date desc. The result would be a list with ALL movies in your database, ordered by the movie title. Each title would appear once for every loan; the loans would be ordered within the movie title, last first. Now, here is the particularity with LEFT JOINS: whenever a movie has NEVER been on loan, it will not be possible to get any values from the loans table. With an INNER JOIN (the type of join I suggest for your problem), the movie would be omitted in the query result, but with a LEFT JOIN, the movie and all its columns will appear once - but the loans columns corresponding to that movie would appear all NULL. Got it? hope I explained more or less well... Honestly, I almost never use LEFT and RIGHT joins, thus, the syntax MIGHT be wrong (I was too lazy to check in the manual :) ) - I'm not a pro in MySQL, so if I explained something wrong, I hope the more experienced members will correct it. Remi Mikalsen E-Mail: [EMAIL PROTECTED] URL: http://www.iMikalsen.com On 8 Oct 2004 at 16:30, Ed Lazor wrote: > Hi Everyone, > > I got excited when I discovered subselects, but quickly discovered that 4.1 > is still in gamma and I can't put it onto my production server. The query I > wanted to use would be great, so maybe there's a way to convert it - since > the manual says most subselects can be done with joins. I'm not sure how > I'd do it though and figured I'd see if anyone here knows how. > > Here's the subselect that I'd like to use: > > select ID from products where CategoryID = (select ID from categories where > ID='21' OR ParentID = '21' ) > > How would I would I create a query using joins that accomplishes the same > result? > > Here's what I was attempting, in case it's close, but it's erroring out: > > select ID from products right join categories where (categories.ID = '21' or > categories.ParentID='21') AND (products.CategoryID = categories.ID or > products.CategoryID = categories.ParentID) > > Thanks, > > Ed > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
