Hemant Singh Hemant Singh - 4 months ago 7
SQL Question

Sql query inner join with LIKE and WHERE

I have 2 tables. One called tbl_issue one called tbl_user. tbl_issue has a field called issue list that contains issues column such as id,uid(user id),pid(project id) etc. tbl_user on the otherhand has a table it holds user details like id(that is user id) u_name(that is user name) p_list(projects id) etc.

i want to show user name according to uid(user id)
code is

SELECT tbl_issue.id, tbl_issue.pid, tbl_issue.uid, tbl_user.u_name
FROM tbl_issue
INNER JOIN tbl_user ON tbl_issue.uid LIKE Concat('%',tbl_user.id,'%')
WHERE uid = $uid;


code example:

SELECT tbl_issue.id, tbl_issue.pid, tbl_issue.uid, tbl_user.u_name
FROM tbl_issue
INNER JOIN tbl_user ON tbl_issue.uid LIKE '%7%'
WHERE uid = '7'`


here uid=7 and tbl_user.id =7 is Lokesh Sharma

this code is given output


id pid uid u_name
14 4 7 admin
14 4 7 ramesh
14 4 7 hemant
14 4 7 lokesh
17 3 7 ravi
17 3 7 lokesh


i want to result like


14 4 7 lokesh
15 4 7 lokesh
16 5 7 lokesh
17 5 7 lokesh
18 3 7 lokesh
19 3 7 lokesh


please help and suggest right way

Answer

You are trying to join tbl_user to tbl_issue using LIKE. What is wrong with just tbl_issue.uid = tbl_user.id?

And your ON condition is pointed on a value, which is either a variable or 7, instead of the column of tbl_user.

SELECT a.id, a.pid, b.id, b.u_name
    FROM tbl_issue a
    LEFT JOIN tbl_user b ON a.uid = b.id
    WHERE b.id = 7