From: Amir Hardon [mailto:[EMAIL PROTECTED] > I have 3 tables: > > main(id int, type tinyint(1)) > categories(id int, name varchar) > items(id int, name varchar) > > I want to select the id and name. > If type is 1 then I want to select the name from categories, > if type is 0 I want to select the name from items, here is > the query I'm trying to use: > > SELECT main.id, IF(main.type,categories.name,items.name), > IF(main.type,"cat","item") AS type FROM main,items,categories WHERE > IF(main.type,categories.id,items.id)=main.id; > > This query gives me each row couple of times, can anyone tell > me why? or can any one give me a better solution?
The problem is that in either case of the first IF, you're still joining on 3 tables when you really want two. If main.type is a category and the WHERE clause ends up evaluating to `WHERE categories.id=main.id' you're still joining on the items table unnecessarily, and vice-versa. Without another WHERE clause to limit it, you're getting the full cartesian product from the first two tables against the third. I don't think this works, but it's essentially what you want: SELECT main.id, IF(main.type,categories.name,items.name), IF(main.type,"cat","item") AS type FROM main, IF(main.type,items,categories) WHERE IF(main.type,categories.id,items.id)=main.id; Note the extra IF in there to join only on /either/ items or categories. I never knew you could use an IF clause to specify a column in a WHERE clause, though, so maybe this will work. It's worh a shot at least. HTH! -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
