I have difficulties with my codes in inner join. I need to join 4 of my tables but it has a error:

"Not unique table/alias: 'tbl_users'"

and when I added alias my table didn't recognized and its attribute.

$sqltran = mysqli_query($con, "
    SELECT  `tbl_users`.`userid` , `tbl_applicant`.`applicant_id`,`tbl_payment`.`userid`

    FROM `tbl_users` , `tbl_applicant` 

    INNER JOIN `tbl_users` ON `tbl_users`.`userid` = `tbl_applicant`.`applicant_id` 

    INNER JOIN `tbl_applicant`  ON `tbl_applicant.applicant_id` = `tbl_payment`.`userid` 

    WHERE `u_id.userid` = '".$_SESSION['userid']."'"
)or die(mysqli_error($con));

$arrVal = array();

thank you!!.. i'm hoping for your answers.


You seem to be referring to the tables too many times. The lines

 FROM `tbl_users` , `tbl_applicant` 
    INNER JOIN `tbl_users`

causes the table tbl_users to be joined with itself, which is probably not what you intend.

Instead, start with tbl_users and then 'join' the other tables one at a time, each time stating which fields you are joining on, like this...

SELECT  tbl_users.userid , tbl_applicant.applicant_id, tbl_payment.userid
FROM tbl_users 
INNER JOIN  tbl_applicant  
       ON tbl_users.userid = tbl_applicant.applicant_id 
INNER JOIN tbl_payment  
      ON tbl_applicant.applicant_id = tbl_payment.userid 
WHERE tbl_users.userid = '".$_SESSION['userid']."'"

Other points are these:

  1. You have

    WHERE `u_id.userid` =

    but you do not include the table u_id in any of your joins. Did you mean tbl_users.userid ?

    If not and u_id is really another table, then you need another JOIN to join it to one of the other three tables on the appropriate column(s).

  2. You do not really need to have all the back ticks in your table/column names. They are only really needed if you have a table with the same name as a keyword or if you have spaces in your column names. Leaving them out makes it easier to read I think.

  3. Although it's good practice to include the table name in your SELECT line you do not need to do so unless the column name is repeated in the other tables, as with tbl_users.userid and tbl_payment.userid. Then using the table name removes ambiguity.

  4. By default, just saying JOIN will do an INNER JOIN and return only those rows whose key appears in both the joined tables.

Incidentally, to answer why you get the Non unique table/alias message, it's because you select tbl_users.userid from tbl_users which you later join to tbl_users again, so it it ambiguous as to which of the two versions of tbl_users you are referring to the SELECT line.

Sometimes it is useful to join a table to itself, as you seem to be inadvertently doing (it's called a self join). However in that case you should include a new table name as an alias to one of the real table names to make them unambiguous. eg

FROM tbl_users
INNER JOIN tbl_users AS copy_tbl_users --  here I am using copy_tbl_users to refer to the tbl_users that I'm joining with.
ON tbl_users.userid = copy_tbl_users.some_id_in_the_table --  Now I use the alias so the table names are unambiguous.

The above code, using AS to make the alias, is for MySQL. Although even in MySQL you don't strictly need the AS keyword but I feel leaving it out for the sake of saving two characters makes the code harder to understand so I always use it.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.