Issue with INNER JOIN and UNION

Question

The query below returns this error:

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in xxx.php on line 21. Fatal error: Call to a member function fetch_assoc() on boolean in xxx.php on line 24

The query only seems to work when I remove the 'userID' column from both SELECT statements. Does this have anything to do with the fact that i'm using 'userID' for my INNER JOIN? How can I fix this issue? Thanks in Advance.

QUERY;

$sql = "SELECT parentID, userID, Rating, Comment, upvote_count, Time FROM     ratings_n_comments
        INNER JOIN user_details
        ON ratings_n_comments.userID=user_details.UserID 
        WHERE ratings_n_comments.mID= '".$mID."'    
        UNION
        SELECT parentID, userID, Rating, Comment, upvote_count, Time FROM replys_to_comments
        INNER JOIN user_details
        ON replys_to_comments.userID=user_details.UserID 
        WHERE replys_to_comments.mID= '".$mID."'";

I think userID is in both of 2 table so you need to specific table in select Example: SELECT parentID, user_details .userID, .... — Akkapong Kajornwongwattana, Jan 19 '17 at 04:38
I don't think there is issue in query looking at you `warnings` and `error`. Methods which you using for executing the result is having issue. **Note:You should use alias for userID while selecting** — Nishant Nair, Jan 19 '17 at 05:34

score 0 · Answer 1 · answered Jan 19 '17 at 04:49

Table user_details and ratings_n_comments have same column name userID, because of ambiguity it will not display the result. so, use user_details.userID or ratings_n_comments.userID in select statement like i am using below

"SELECT parentID, user_details.userID, Rating, Comment, upvote_count, Time FROM     ratings_n_comments
        INNER JOIN user_details
        ON ratings_n_comments.userID=user_details.UserID 
        WHERE ratings_n_comments.mID= '".$mID."'    
        UNION
        SELECT parentID, user_details.userID, Rating, Comment, upvote_count, Time FROM replys_to_comments
        INNER JOIN user_details
        ON replys_to_comments.userID=user_details.UserID 
        WHERE replys_to_comments.mID= '".$mID."'";

Issue with INNER JOIN and UNION

1 Answers1