Thanks Remi =) I just ran a test and it worked. Honestly, I'm still a little stumped on why it works, but I'll keep playing with it for a while to see if I can get it. I'll either eventually figure it out or come back in frustration asking for more help ;) hehe
> -----Original Message----- > From: Remi Mikalsen [mailto:[EMAIL PROTECTED] > Sent: Friday, October 08, 2004 5:07 PM > To: [EMAIL PROTECTED] > Subject: Re: Convert subselect query to pre-subselect query > > 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] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
