Issue in query with inner join and concat() operation in mysql

Question

I have two tables:

sk_accounts      //details of user

acnt_user_id
acnt_fname //first name
acnt_lname
acnt_profile_picture
acnt_member_class

so on........

 sk_following   //table containing details of users who are following others

id
flwing_follower_id //id of the user who are followed by other followers
flwing_following_user_id

following_date

I want to display details of the follower based on the following Mysql code.Unfortunately it returns zero rows eventhough there are 3 rows.My query is like this:

$query_following = "SELECT sk_following.flwing_following_user_id, 
sk_accounts.acnt_fname,
sk_accounts.acnt_lname,
sk_accounts.acnt_member_class,
sk_accounts.acnt_profile_picture
FROM sk_following 
INNER JOIN sk_accounts 
WHERE sk_following.flwing_follower_id='$id' AND        sk_accounts.acnt_user_id=sk_following.flwing_following_user_id AND CONCAT(sk_accounts.acnt_fname,' ',sk_accounts.acnt_lname)='$name'";
$result_following = mysql_query($query_following);
$count_following = mysql_num_rows($result_following);
echo $count_following;

Note:$id and $name contain values Kindly help me.Thanks in advance.

Can you confirm that flwing_following_user_id is a reference to acnt_user_id field table? — sdespont, Jan 31 '13 at 06:51
@sdespont Ya,I have this query worked in another context without using concat() — Techy, Jan 31 '13 at 06:54
try Applying WHERE clauses one by one..u will realize where is going wrong.. — iJay, Jan 31 '13 at 06:56
Interresting! And are you reeeeeeeeeeeeealy sure that your $name is exactly the same than your CONCAT result? Char case included? — sdespont, Jan 31 '13 at 06:57
@Irantha ya i am checking,I thinks its my fault in WHERE clause — Techy, Jan 31 '13 at 06:57
@sdespont I have checked the WHERE clause,It is having some error.Its my fault — Techy, Jan 31 '13 at 06:58
@AdvaitAmin Ya in the WHERE sk_following.flwing_follower_id='$id' — Techy, Jan 31 '13 at 07:02

score 2 · Answer 1 · answered Jan 31 '13 at 06:53

2

Try this,

"SELECT sk_following.flwing_following_user_id,
sk_accounts.acnt_fname,
sk_accounts.acnt_lname,
sk_accounts.acnt_member_class,
sk_accounts.acnt_profile_picture 
FROM sk_following
LEFT JOIN sk_accounts ON sk_accounts.acnt_user_id=sk_following.flwing_following_user_id
WHERE sk_following.flwing_follower_id='$id'
AND CONCAT(sk_accounts.acnt_fname,' ',sk_accounts.acnt_lname)='$name'";

may this help you.

answered Jan 31 '13 at 06:53

Tony Stark

8,064
8
44
63

Use left join because its better than inner join. see http://stackoverflow.com/questions/2726657/inner-join-vs-left-join-performance-in-sql-server – Tony Stark Jan 31 '13 at 07:04
@Anaz A you get it about left join or not? i think its better to use left join. i also use more left join then inner join. – Tony Stark Jan 31 '13 at 07:12

score 1 · Accepted Answer · answered Jan 31 '13 at 06:44

Hard to completely understand without seeing sample data and desired output, but should your JOIN be on the flwing_follower_id and not the flwing_following_user_id?

SELECT sk_following.flwing_following_user_id,
    sk_accounts.acnt_fname,
    sk_accounts.acnt_lname,
    sk_accounts.acnt_member_class,
    sk_accounts.acnt_profile_picture 
FROM sk_following 
    INNER JOIN sk_accounts ON sk_accounts.acnt_user_id=sk_following.flwing_follower_id 
WHERE sk_following.flwing_follower_id='$id' 
    AND CONCAT(sk_accounts.acnt_fname,' ',sk_accounts.acnt_lname)='$name'

Good luck.

I want to display all the above data in a div. – Techy Jan 31 '13 at 06:48 — Techy, Jan 31 '13 at 06:48

Issue in query with inner join and concat() operation in mysql

2 Answers2