blacknight Posted September 19, 2011 Share Posted September 19, 2011 the title if this is the best i could do i think because im lost in what i need to do .. theres the info .. The main query left joins 5 tables one of these tables may or may not have data in it (here inlies my issue) this is the query.. SELECT `members`.`member_id`, `members`.`name`, `members`.`class`, `members`.`classid`, `members`.`level`, `members`.`zone`, `members`.`online`, `members`.`last_online`, UNIX_TIMESTAMP(`members`.`last_online`) AS 'last_online_stamp', DATE_FORMAT( DATE_ADD(`members`.`last_online`,INTERVAL 0 HOUR ),'%a %b %D,%l:%i %p' ) AS 'last_online_format', `members`.`note`, `members`.`guild_title`, `alts`.`main_id`, `guild`.`update_time`, `guild`.`factionEn`, IF( `members`.`note` IS NULL OR `members`.`note` = '',1,0 ) AS 'nisnull', `members`.`officer_note`, IF( `members`.`officer_note` IS NULL OR `members`.`officer_note` = '',1,0 ) AS 'onisnull', `rep`.`curr_rep`, `rep`.`max_rep`, `rep`.`AtWar`, `rep`.`Standing`, `rep`.`name` AS 'repname', IF( `rep`.`Standing` IS NULL OR `rep`.`Standing` = '',1,0 ) AS `repisnull`, `members`.`guild_rank`, `players`.`server`, `players`.`race`, `players`.`sex`, `players`.`exp`, `players`.`clientLocale`, `players`.`lifetimeRankName`, `players`.`lifetimeHighestRank`, IF( `players`.`lifetimeHighestRank` IS NULL OR `players`.`lifetimeHighestRank` = '0',1,0 ) AS 'risnull', `players`.`hearth`, IF( `players`.`hearth` IS NULL OR `players`.`hearth` = '',1,0 ) AS 'hisnull', UNIX_TIMESTAMP( `players`.`dateupdatedutc`) AS 'last_update_stamp', DATE_FORMAT( DATE_ADD(`players`.`dateupdatedutc`,INTERVAL 0 HOUR ),'%a %b %D,%l:%i %p' ) AS 'last_update_format', IF( `players`.`dateupdatedutc` IS NULL OR `players`.`dateupdatedutc` = '',1,0 ) AS 'luisnull', GROUP_CONCAT( DISTINCT CONCAT( `proftable`.`skill_name` ,'|',`proftable`.`skill_level` ) ORDER BY `proftable`.`skill_order`) as professions, GROUP_CONCAT( DISTINCT CONCAT( `talenttable`.`build`,'|',`talenttable`.`tree` ,'|',`talenttable`.`pointsspent` ,'|',`talenttable`.`background` ) ORDER BY`talenttable`.`build`,`talenttable`.`order`) AS 'talents' FROM `roster_members` AS members LEFT JOIN `roster_players` AS players ON `members`.`member_id` = `players`.`member_id` LEFT JOIN `roster_skills` AS proftable ON `members`.`member_id` = `proftable`.`member_id` LEFT JOIN `roster_talenttree` AS talenttable ON `members`.`member_id` = `talenttable`.`member_id` LEFT JOIN `roster_addons_memberslist_alts` AS alts ON `members`.`member_id` = `alts`.`member_id` LEFT JOIN `roster_guild` AS guild ON `members`.`guild_id` = `guild`.`guild_id` LEFT JOIN `roster_reputation` AS rep ON `members`.`member_id` = `rep`.`member_id` WHERE (`members`.`guild_id` = "11" ) GROUP BY `members`.`member_id` ORDER BY IF(`members`.`member_id` = `alts`.`member_id`,1,0),`members`.`guild_rank` ASC,`members`.`level` DESC,`members`.`name` ASC LIMIT 0,75 i know its a lota code huh this displayes the proper settings but `rep` is selecting random info from the table if the user has any if not its null and no info is provided. i need to select a specific collum value of `rep.`name` but doing so only shows the `members` with that data i want to show everyone else as well and have null values for them but display the info for the people that do i hope this makes sence and someone can help me .. Quote Link to comment Share on other sites More sharing options...
blacknight Posted September 19, 2011 Author Share Posted September 19, 2011 i solved it used where clasuse `rep`.`name` = 'rep name' OR `rep`.`name` IS NULL Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.