Here's my dilemna (NOTE: the mysql version im on is 3.22.25, blame communitech.net, not me) I've created a forum system using php and is extensible to use any DB....im currently working on the mysql version.... A pretty standard part of any forum system ive seen is rankings (look here for example)....ive defined both standard and custom rankings in two separate tables sr_rankings (unique_id int, title varchar(xx)) spc_rankings (user_id_of_user, spc_title varchar(xx)) my users table has a field called rank_val which may contain a value from 0 to "NUMBER_OF_STANDARD_RANKS_DEFINED" if it is 0 this means they should have a customized title and of course if its positive that val corresponds to the unique_id in the sr_rankings table so heres my dilemna...in ONE query....how can I CONDITIONALLY join to either the sr_rankings table or the spc_rankings table? basically im saying if the rank_val is 0 i should join to the spc_rankings table and if its positive i should join to the std_rankings table heres the current query and it ALMOST works select p.cat_id, p.forum_num, p.num_id, p.user, p.message, p.message_icon, DATE_FORMAT(p.time_added, '%m-%d-%Y %r'), p.auth_id, pu.num_posts, pu.residence, pu.fav_site, pu.email, pu.username, DATE_FORMAT(pu.registered, '%M %D %Y'), pu.signature, p.sig, if(pu.rank_val, std.rnk_title, spc.spc_title) AS title from post p, post_users pu, std_rankings std, spc_rankings spc where p.auth_id = pu.userid and p.cat_id=\"$c\" and p.forum_num = \"$f\" and p.num_id=\"$n\" and p.auth_id = pu.userid and (pu.rank_val = std.id OR pu.userid = spc.user_id) order by p.time_added limit 25 Three problems: 1) If a user does have a special ranking the post gets repeated 14 times (14 being the number of rank_levels I have defined in the std_rankings table) and 2) Is there a way to ONLY join to the special or standard table in someway so as not to join to a table for no reason? 3) I'm sure there has to be a better, more efficient, closer to ansi standards way of doing this. Can anyone give me a hand with this? I've been banging away at this for a day or two now and I'm really stumped. Thanks for any advice, CG --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
