Why Does This SQL Code Give Error 1066 (Not Unique Table/Alias: 'User')

Not unique table/alias

Your query contains columns which could be present with the same name in more than one table you are referencing, hence the not unique error. It's best if you make the references explicit and/or use table aliases when joining.

Try

    SELECT pa.ProjectID, p.Project_Title, a.Account_ID, a.Username, a.Access_Type, c.First_Name, c.Last_Name
      FROM Project_Assigned pa
INNER JOIN Account a
        ON pa.AccountID = a.Account_ID
INNER JOIN Project p
        ON pa.ProjectID = p.Project_ID
INNER JOIN Clients c
        ON a.Account_ID = c.Account_ID
     WHERE a.Access_Type = 'Client';

MySQL Syntax error or access violation: 1066 Not unique table/alias: 'users'

Alias the user tables u1 and u2 to resolve the error.

inner join `users` u1 on `teams`.`playerOneId` = u1.`id` 
inner join `users` u2 on `teams`.`playerTwoId` =  u2.`id` 

Error Code: 1066. Not unique table/alias: 'ordertbl' - Not able to Perform Inner Join MYSQL

I think your inner join syntax is incorrect.
Please try below-

select
     o.OrdNo, o.OrdDate, c.CustNo, c.CustFirstName, c.CustLastName 
from ordertbl as o
inner join customer as c on c.CustNo = o.CustNo
WHERE o.ordDate like '%2030-01%' 
      AND c.custstate ='CO' 
      AND o.OrdState ='WA';

Not unique table alias

@Dravenzo you are using multiple instances of the table Child and giving it the alias of C both times:

set @carer_fname = 'Carername';
set @carer_sname = 'Carersname';
select concat_ws(' ', C.child_fname, C.child_sname) as 'Child Name'
,C.child_carer
,A.activity_name, A.activity_day
from -->Child C<--, Activity A -- first instance of aliasing Child as C
inner join -->Child C<-- on CA.child_id = C.child_id -- second instance of aliasing Child as C
inner join Activity A on CA.activity_id = A.activity_id
order by child_sname

It's not really clear what you are trying to do with this query because in your FROM clause you are listing both tables Child and Activity and then using JOIN statements in a confusing manner. Also, you are referring to an alias of CA but you aren't stating which table should be aliased as CA.

This is only a guess at what you want to do with your query because of the confusing aspects of what you have listed, but try something like this:

set @carer_fname = 'Carername';
set @carer_sname = 'Carersname';
select concat_ws(' ', C.child_fname, C.child_sname) as 'Child Name',
    C.child_carer,
    A.activity_name, 
    A.activity_day
from Child C
inner join Activity A on A.activity_id = C.activity_id
order by C.child_sname

I suggest reading up on MySQL joins in the official documentation here or googling MySQL JOINS tutorial, there are loads of tutorials out there.

Why does this SQL code give error 1066 (Not unique table/alias: 'customer')?

You have listed the table customer twice in your FROM statement. Here's the fixed version:

SELECT customer.id, customer.firstName, account.id
FROM account
INNER JOIN customer
ON customer.id = account.customerId
ORDER BY customer.id

Syntax error or access violation: 1066 Not unique table/alias: '

You are trying to join medicals table twice so in this case you need to provide unique alias to each instance and in join clause specify that alias for columns so that database will know the exact column from table to use in filter like

Patients::where(function ($q) use ($startdate, $enddate, $id) {
            $q->where("patient_referral.trx_id",  $id);
        })
        ->leftJoin("medicals as a", "a.patients_id", "=", "patients.id")
        ->leftJoin("medicals as b", "b.trx_id", "=", "patient_referral.trx_id")
        ->get();

Error Code: 1066. Not unique table/alias: 'File'

Since you have three File tables in your query, MySQL has no way to know which one you mean when you say File. You'd normally fix that assigning different table aliases. But it feels rather weird that you actually need to join the same table three times. I have the impression you only want it once, as in:

SELECT Transaction.TransactionID, Transaction.TransactionDate, Transaction.Amount, TransactionDescription.Description
,File.StartDate, File.EndDate, File.Filename
FROM Transaction
INNER JOIN TransactionDescription ON  TransactionDescription.Description = TransactionDescription.Description
INNER JOIN File ON  File.StartDate = File.StartDate
    AND File.EndDate = File.EndDate
    AND File.Filename = File.Filename

Why does this sql #1066 - Not unique table/alias: 'categories'?

That's 'cause you are joining on the same table, which needs a table alias in order to avoid confusion, as illustrated below

FROM
    categories
INNER JOIN
    categories

Change it to below (here c1, c2 are table aliases)

FROM
    categories c1
INNER JOIN
    categories c2 
ON
    c1.parent = c2.id

and adjust the SELECT clause accordingly

---

Related Topics