0

i have 2 table , awards and awards_user , and have problem with LEFT JOIN on mysql query do not get results like i want

awards :

awardid  | name   | link  
1        | award1 | photo link    
2        | award2 | photo link   
3        | award3 | photo link   
4        | award4 | photo link

awards_user :

awardid  | username
2        | mark      
2        | jon
3        | mark    
1        | jon

I try to get results like that :

award name  |  award photo  |  Users
award1      |      link     |  jon
award2      |      link     |  mark,jon
award3      |      link     |  mark
award4      |      link     |  -------

that is my try :

 SELECT au.username 
      , au.userid 
      , a.link 
      , a.name 
      , a.awardid 
   FROM awards a
   LEFT 
   JOIN awards_user au
     ON au.awardid = a.awardid
  WHERE a.forumid = 22
  GROUP 
     BY a.awardid 
      , au.username
  ORDER 
     BY a.awardid DESC

the Solve the problem:

$all_awards = $db->query_read(" SELECT awards.name as name, awards.link as link, 
       GROUP_CONCAT(DISTINCT awards_user.username) AS username
   FROM awards LEFT JOIN awards_user ON (awards_user.awardid = awards.awardid)
   where awards.forumid = '".$_REQUEST['forumid']."'
   GROUP BY awards.awardid, awards.name, awards.link
  ");
JasonMArcher
  • 14,195
  • 22
  • 56
  • 52
o6 qr
  • 95
  • 2
  • 7
  • 3
    If you want a result like `award name | award photo | Users`, don't you think it might be an idea to SELECT *those* columns? – Strawberry Jun 08 '14 at 21:47
  • i don't understand you , i must use `left join` , becouse there users have more on of awards – o6 qr Jun 08 '14 at 21:49
  • That's not how we roll – Strawberry Jun 08 '14 at 21:49
  • Do not place `$_REQUEST['forumid']` into SQL query strings. Otherwise I'll CV against this: [How can I prevent SQL-injection in PHP?](http://stackoverflow.com/q/60174/367456) - Also your question is not clear. You want something, however you didn't share what you get instead and what your understanding of the difference is. – hakre Jun 08 '14 at 21:53

3 Answers3

0

GROUP_CONCAT does exactly what you need:

SELECT
       a.name AS name
     , a.link AS photo
     , GROUP_CONCAT(DISTINCT au.username) AS users
FROM awards AS a
     LEFT JOIN awards_user AS au ON (au.awardid = a.awardid)
GROUP BY
       a.awardid
     , a.name
     , a.link
ORDER BY
     a.awardid DESC

More info about MySQL GROUP_CONCAT here: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

BTW, you can even sort usernames or choose different separator if you want in group_concat. Just read MySQL manual and google "mysql group_concat examples" :)

uchar
  • 2,552
  • 4
  • 29
  • 50
DarkSide
  • 3,670
  • 1
  • 26
  • 34
  • okay i try it , but same problem when i get username of users , only show 1 user , i want show all user Who got same award `(jon,mark,elc ..)` – o6 qr Jun 08 '14 at 22:06
  • sorry , it's worked , but i have now new problem ... i want put userid on every user – o6 qr Jun 08 '14 at 22:49
  • I don't see user_id field in your sample data. Anyway you can do that by adding one more group_concat. On the other hand - are you sure what you're doing exactly? What's the point to get usernames and ids as CSV? – DarkSide Jun 09 '14 at 08:54
  • i want get all awards and what users Holders of each, anyway i found the The correct answer, thank you – o6 qr Jun 15 '14 at 01:36
0

and also add `` on table names because your naming conventions are pretty rough might match with mysql reserved words and again PRACTISE SECURITY FILTER YOUR INPUTS $_REQUEST['forumid']

uchar
  • 2,552
  • 4
  • 29
  • 50
kay
  • 337
  • 3
  • 7
0

Try this:

SELECT awards.name as award_name, award.link as award_photo, 
       GROUP_CONCAT(DISTINCT awards_user.username AS users
   FROM awards LEFT JOIN awards_user ON (awards_user.awardid = awards.awardid)
   GROUP BY awards.awardid, awards.name, awards.link;
rms
  • 40
  • 4
  • Oops, @DarkSide beat me to it while I was typing -- and his looks great. – rms Jun 08 '14 at 22:04
  • and you forgot closing brackets after group_concat :) – DarkSide Jun 08 '14 at 22:04
  • i fix some Mistakes but still show me error `$all_awards = $db->query_read(" SELECT awards.name as name, awards.link as link, GROUP_CONCAT(DISTINCT awards_user.username AS username) FROM awards LEFT JOIN awards_user ON (awards_user.awardid = awards.awardid) GROUP BY awards.awardid, awards.name, awards.link ");` – o6 qr Jun 08 '14 at 22:12
  • okay i fix the error and i think working good [forgot closing brackets after group_concat] `$all_awards = $db->query_read(" SELECT awards.name as name, awards.link as link, GROUP_CONCAT(DISTINCT awards_user.username) AS username FROM awards LEFT JOIN awards_user ON (awards_user.awardid = awards.awardid) where awards.forumid = '".$_REQUEST['forumid']."' GROUP BY awards.awardid, awards.name, awards.link ");` – o6 qr Jun 08 '14 at 22:18