Re: Query with IF acting wierd.

[Michael Stassen]

Mike Johnson wrote:

From: Mike Johnson 

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;
Oh, my mistake. I just realized I reversed items and categories in the IF clause.

Try this instead:

SELECT main.id, 
IF(main.type,categories.name,items.name), 
IF(main.type,"cat","item") AS type 
FROM main, IF(main.type,categories,items) WHERE 
IF(main.type,categories.id,items.id)=main.id;

Sorry!

Actually, now I'm really curious if this works or not. Let me know how it turns out.
Unfortunately, that won't work.  IF can return a value, not a column 
reference.

As I understand it, you want to join main to categories for rows where 
main.type is 1, but you want to join main to items for rows where 
main.type is 0.  As Mike explained, these are two separate 2-table 
joins, not one 3-table join.  You didn't say which version of mysql you 
have.  If you have at least 4.0.0, you can combine the results of 2 
selects with UNION, so this should work:

 SELECT main.id, categories.name, 'cat ' AS type
  FROM main, categories
  WHERE main.id = categories.id AND main.type
UNION
 SELECT main.id, items.name, 'item' AS type
  FROM main, items
  WHERE main.id = items.id AND NOT main.type;
If you are still using 3.23.x, you can accomplish the same thing with a 
temporary table.

 CREATE TEMPORARY TABLE joins
  SELECT main.id, categories.name, 'cat ' AS type
  FROM main, categories
  WHERE main.id = categories.id AND main.type;
 INSERT INTO joins
  SELECT main.id, items.name, 'item' AS type
  FROM main, items
  WHERE main.id = items.id AND NOT main.type;
 SELECT * FROM joins;

Michael

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