Error code 1066 not unique table alias

This is my table structure: The error message is: #1066 - Not unique table/alias: 'user' The following is my code. SELECT article.* , section.title, category.title, user.name, user.name FROM

Your error is because you have:

     JOIN user ON article.author_id = user.id
LEFT JOIN user ON article.modified_by = user.id

You have two instances of the same table, but the database can’t determine which is which. To fix this, you need to use table aliases:

     JOIN USER u ON article.author_id = u.id
LEFT JOIN USER u2 ON article.modified_by = u2.id

It’s good habit to always alias your tables, unless you like writing the full table name all the time when you don’t have situations like these.

The next issues to address will be:

SELECT article.* , section.title, category.title, user.name, user.name

1) Never use SELECT * — always spell out the columns you want, even if it is the entire table. Read this SO Question to understand why.

2) You’ll get ambiguous column errors relating to the user.name columns because again, the database can’t tell which table instance to pull data from. Using table aliases fixes the issue:

SELECT article.* , section.title, category.title, u.name, u2.name

I get the error ERROR 1066 (42000): Not unique table/alias:

I cant figure out whats wrong with it.

SELECT Project_Assigned.ProjectID, Project_Title, Account.Account_ID, Username, Access_Type
FROM Project_Assigned 
JOIN Account 
  ON Project_Assigned.AccountID = Account.Account_ID
JOIN Project
  ON Project_Assigned.ProjectID = Project.Project_ID
where Access_Type = 'Client';

Harikrishnan's user avatar

Harikrishnan

9,4729 gold badges86 silver badges127 bronze badges

asked Nov 10, 2011 at 18:37

Malcr001's user avatar

2

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';

answered Nov 10, 2011 at 19:17

Shef's user avatar

ShefShef

44.4k15 gold badges78 silver badges90 bronze badges

7

 select persons.personsid,name,info.id,address
    -> from persons
    -> inner join persons on info.infoid = info.info.id;

answered Jul 17, 2017 at 20:37

user8321755's user avatar

1

I had this error, and the cause was an incorrect string concatenation. I was joining two sql string and forgot to put a space between them.

answered Aug 18, 2022 at 5:39

Michael Berry's user avatar

1

I have two MySQL statemenents:

SELECT PRODUCTS.REFERENCE,PRODUCTS.NAME,PRODUCTS.PRICEBUY,PRODUCTS.PRICESELL,
       SUM(TICKETLINES.UNITS) AS UNITS,
       SUM(TICKETLINES.PRICE * TICKETLINES.UNITS) AS SUBTOTAL,
       SUM((TICKETLINES.PRICE * TICKETLINES.UNITS) * TAXES.RATE) As TAXES,
       SUM(TICKETLINES.PRICE * TICKETLINES.UNITS)
        + SUM((TICKETLINES.PRICE * TICKETLINES.UNITS) * TAXES.RATE) AS GROSSTOTAL
FROM TICKETLINES
LEFT OUTER JOIN PRODUCTS ON TICKETLINES.PRODUCT = PRODUCTS.ID
LEFT OUTER JOIN TICKETS ON TICKETS.ID = TICKETLINES.TICKET
LEFT OUTER JOIN RECEIPTS ON RECEIPTS.ID = TICKETS.ID, TAXES
WHERE RECEIPTS.ID = TICKETS.ID AND TICKETS.ID = TICKETLINES.TICKET
AND TICKETLINES.PRODUCT = PRODUCTS.ID
AND TICKETLINES.TAXID = TAXES.ID
GROUP BY PRODUCTS.REFERENCE, PRODUCTS.NAME,PRODUCTS.PRICEBUY,PRODUCTS.PRICESELL
ORDER BY GROSSTOTAL DESC
LIMIT 10

and

SELECT PRODUCTS.ID, PRODUCTS.REFERENCE, PRODUCTS.CODE, PRODUCTS.NAME, PRODUCTS.ISCOM,
       PRODUCTS.ISSCALE, PRODUCTS.PRICEBUY, PRODUCTS.PRICESELL, PRODUCTS.TAXCAT,
       PRODUCTS.CATEGORY,PRODUCTS.ATTRIBUTESET_ID, PRODUCTS.IMAGE, PRODUCTS.ATTRIBUTES,
       PRODUCTS.ISKITCHEN, PRODUCTS.ISSERVICE, PRODUCTS.DISPLAY, PRODUCTS.ISVPRICE,
       PRODUCTS.ISVERPATRIB, PRODUCTS.TEXTTIP, PRODUCTS.WARRANTY, PRODUCTS.STOCKUNITS,
       TAXES.NAME, TAXES.RATE, PRODUCTS.STOCKVOLUME
FROM PRODUCTS
INNER JOIN PRODUCTS_CAT ON PRODUCTS.ID=PRODUCTS_CAT.PRODUCT
JOIN TAXCATEGORIES ON PRODUCTS.TAXCAT=TAXCATEGORIES.ID
JOIN TAXES ON TAXCATEGORIES.ID=TAXES.ID
ORDER BY PRODUCTS.NAME

Now, I am trying to combine these two statements into one, here is what I’ve got:

SELECT PRODUCTS.ID, PRODUCTS.REFERENCE, PRODUCTS.CODE, PRODUCTS.NAME, PRODUCTS.ISCOM,
       PRODUCTS.ISSCALE, PRODUCTS.PRICEBUY, PRODUCTS.PRICESELL, PRODUCTS.TAXCAT,
       PRODUCTS.CATEGORY, PRODUCTS.ATTRIBUTESET_ID, PRODUCTS.IMAGE, PRODUCTS.ATTRIBUTES,
       PRODUCTS.ISKITCHEN, PRODUCTS.ISSERVICE, PRODUCTS.DISPLAY, PRODUCTS.ISVPRICE,
       PRODUCTS.ISVERPATRIB, PRODUCTS.TEXTTIP, PRODUCTS.WARRANTY, PRODUCTS.STOCKUNITS,
       TAXES.NAME, TAXES.RATE, PRODUCTS.STOCKVOLUME,
       SUM(TICKETLINES.UNITS) AS UNITS,
       SUM(TICKETLINES.PRICE*TICKETLINES.UNITS) AS SUBTOTAL,
       SUM((TICKETLINES.PRICE*TICKETLINES.UNITS)*TAXES.RATE) AS TAXESTOTAL,
       SUM(TICKETLINES.PRICE*TICKETLINES.UNITS)
         +SUM((TICKETLINES.PRICE*TICKETLINES.UNITS)*TAXES.RATE) AS GROSSTOTAL
FROM TICKETLINES
INNER JOIN PRODUCTS_CAT ON PRODUCTS.ID=PRODUCTS_CAT.PRODUCT
JOIN TAXCATEGORIES ON PPRODUCTS.TAXCAT=TAXCATEGORIES.ID
JOIN TAXES ON TAXCATEGORIES.ID=TAXES.ID
LEFT OUTER JOIN PRODUCTS ON TICKETLINES.PRODUCT=PRODUCTS.ID
LEFT OUTER JOIN TICKETS ON TICKETS.ID=TICKETLINES.TICKET
LEFT OUTER JOIN RECEIPTS ON RECEIPTS.ID=TICKETS.ID, TAXES
WHERE RECEIPTS.ID=TICKETS.ID AND TICKETS.ID=TICKETLINES.TICKET
AND TICKETLINES.PRODUCT=PRODUCTS.ID
AND TICKETLINES.TAXID=TAXES.ID
ORDER BY PRODUCTS.NAME

Why upper statement does not execute and reports error Error Code: 1066. Not unique table/alias: 'TAXES'
?

Hi,

I had been trying to use belongsToMany relationship for following tables

faculties table
id
name

proficiencies table
id
name

faculty_proficiencies table
faculty_id
proficiency_id

Following is my model files

<?php

namespace AppModelsMT;

use AppModelsMTFacultyProficiency;
use IlluminateDatabaseEloquentModel;

class Faculty extends Model
{
    protected $table = "faculties";

    protected $fillable = ['name'];

    public function proficiency() {
        return $this->belongsToMany(FacultyProficiency::class, 'faculty_proficiencies', 'faculty_id', 'proficiency_id');
    }
}


<?php

namespace AppModelsMT;

use IlluminateDatabaseEloquentModel;

class FacultyProficiency extends Model
{
    protected $table = "faculty_proficiencies";

    protected $fillable = ['faculty_id', 'proficiency_id'];

    public $timestamps = false;

    protected function faculty() {
        return $this->belongsTo(AppModelsMTFaculty::class, 'faculty_id');
    }

    protected function proficiency() {
        return $this->belongsTo(AppModelsMTProficiency::class, 'proficiency_id');
    }
}

<?php

namespace AppModelsMT;

use IlluminateDatabaseEloquentModel;

class Proficiency extends Model
{
    protected $table = "proficiencies";

    protected $fillable = ['name'];

    public $timestamps = false;

    public function faculty() {
        return $this->belongsToMany(FacultyProficiency::class, 'faculty_proficiencies', 'proficiency_id', 'faculty_id');
    }
}

Now, when I try getting faculty with proficiency like the following,

I get following error

Syntax error or access violation: 1066 Not unique table/alias: 'faculty_proficiencies' 
(SQL: 
select 
    `faculty_proficiencies`.*, 
    `faculty_proficiencies`.`faculty_id` as `pivot_faculty_id`, 
    `faculty_proficiencies`.`proficiency_id` as `pivot_proficiency_id` 
from 
    `faculty_proficiencies` 
inner join 
    `faculty_proficiencies` 
on 
    `faculty_proficiencies`.`id` = `faculty_proficiencies`.`proficiency_id` 
where 
    `faculty_proficiencies`.`faculty_id` = 5) 

So, I am receiving the following error from Laravel:

SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'participants' (SQL: select `participants`.*, `participants`.`message_id` as `pivot_message_id`, `participants`.`user_id` as `pivot_user_id`, `participants`.`created_at` as `pivot_created_at`, `participants`.`updated_at` as `pivot_updated_at` from `participants` inner join `participants` on `participants`.`id` = `participants`.`user_id` where `participants`.`deleted_at` is null and `participants`.`message_id` in (2))

My message/participants relatioship looks like this:

public function participants()
    {
        return $this->belongsToMany('NamespaceModulesEmailModelsParticipant', 'participants', 'message_id', 'user_id')->withTimestamps();
    }

and I am trying to call it like this:

public function getAllMessages()
{
    return Message::with('user')->with('participants')->get();
}

Why am I getting this error? What is going on?

Edit: Included full models

Message class Message extends Eloquent { use PublishedTrait; use SoftDeletingTrait;

    /**
     * The database table used by the model.
     *
     * @var string
     */
    protected $table = 'messages';

    /**
     * The attributes that can be set with Mass Assignment.
     *
     * @var array
     */
    protected $fillable = ['subject', 'user_id', 'body', 'status'];

    /**
     * The attributes that should be mutated to dates.
     *
     * @var array
     */
    protected $dates = ['created_at', 'updated_at', 'deleted_at'];

    /**
     * Validation rules.
     *
     * @var array
     */
    protected $rules = [
        'subject' => 'required|max:255',
        'body' => 'required',
    ];

    /**
     * User relationship
     *
     * @return IlluminateDatabaseEloquentRelationsBelongsTo
     */
    public function user()
    {
        return $this->belongsTo(Config::get('email.user_model'));
    }

    public function assets()
    {
        return $this->belongsToMany('NamespaceModulesAssetsModelsAsset', 'message_assets');
    }

    /**
     * Participants relationship
     *
     * @return IlluminateDatabaseEloquentRelationsHasMany
     */
    public function participants()
    {
        return $this->belongsToMany('NamespaceModulesEmailModelsParticipant', 'participants', 'message_id', 'user_id')->withTimestamps();
    }

    /**
     * Recipients of this message
     *
     * @return IlluminateDatabaseEloquentRelationsHasMany
     */
    public function recipients()
    {
        return $this->participants()->where('user_id', '!=', $this->user_id);
    }

    /**
     * Returns the latest message from a thread
     *
     * @return NamespaceModulesEmailModelsMessage
     */
    public function getLatestMessageAttribute()
    {
        return $this->messages()->latest()->first();
    }

    /**
     * Returns threads that the user is associated with
     * @param $query
     * @param $userId
     * @return mixed
     */
    public function scopeForUser($query, $userId)
    {
        return $query->join('participants', 'messages.id', '=', 'participants.message_id')
            ->where('participants.user_id', $userId)
            ->where('participants.deleted_at', null)
            ->select('messages.*');
    }

    /**
     * Returns threads that the user is associated with
     * @param $query
     * @param $userId
     * @return mixed
     */
    public function scopeForUserWithDeleted($query, $userId)
    {
        return $query->join('participants', 'messages.id', '=', 'participants.message_id')
            ->where('participants.user_id', $userId)
            ->select('messages.*');
    }

    /**
     * Returns messages that the user has sent
     * @param $query
     * @param $userId
     * @return mixed
     */
    public function scopeByUser($query, $userId)
    {
        return $query->where('user_id', $userId);
    }

    /**
     * Returns threads with new messages that the user is associated with
     * @param $query
     * @param $userId
     * @return mixed
     */
    public function scopeForUserWithNewMessages($query, $userId)
    {
        return $query->join('participants', 'messages.id', '=', 'participants.message_id')
            ->where('participants.user_id', $userId)
            ->whereNull('participants.deleted_at')
            ->where(function ($query) {
                $query->where('messages.updated_at', '>', $this->getConnection()->raw($this->getConnection()->getTablePrefix() . 'participants.last_read'))
                    ->orWhereNull('participants.last_read');
            })
            ->select('messages.*');
    }

}

Participant

class Participant extends Eloquent
{
    use SoftDeletingTrait;

    /**
     * The database table used by the model.
     *
     * @var string
     */
    protected $table = 'participants';

    /**
     * The attributes that can be set with Mass Assignment.
     *
     * @var array
     */
    protected $fillable = ['message_id', 'user_id', 'last_read'];

    /**
     * The attributes that should be mutated to dates.
     *
     * @var array
     */
    protected $dates = ['created_at', 'updated_at', 'deleted_at', 'last_read'];

    /**
     * Thread relationship
     *
     * @return IlluminateDatabaseEloquentRelationsBelongsTo
     */
    public function message()
    {
        return $this->hasMany('NamespaceModulesEmailModelsMessage');
    }

    /**
     * User relationship
     *
     * @return IlluminateDatabaseEloquentRelationsBelongsTo
     */
    public function user()
    {
        return $this->belongsTo(Config::get('email.user_model'));
    }
}


Answered via the Larachat official Slack:

The relationship is missing a pivot table for this to work. The second argument in the participants method is the pivot table to use:

public function participants()
{
    return $this->belongsToMany('NamespaceModulesEmailModelsParticipant', 'PIVOT', 'message_id', 'user_id')->withTimestamps();
}

Therefore, you can’t use participants as the pivot because it is one of the tables in the relationship, you need a message_participant pivot table.

Здравствуйте.
Возникла такая проблема.
При запросе:

mysql> SELECT lr1_1.number, lr1_1.fio, lr1_2.code, lr1_2.name, lr1_4.auditory, lr1_3.code, lr1_3.name
    -> FROM lr1_1 INNER JOIN lessons ON lr1_1.number = lessons.teacher
    -> INNER JOIN lessons ON lr1_2.code = lessons.lesson
    -> INNER JOIN lessons ON lr1_4.auditory = lessons.auditory
    -> INNER JOIN lessons ON lr1_3.code = lessons.cafedry;
 

Выдается ошибка:

ERROR 1066 (42000): Not unique table/alias: ‘lessons’

Структура таблиц:

mysql> DESCRIBE lr1_1;
+———+————-+——+——+———+——-+
| Field   | Type        | Null | Key | Default | Extra |
+———+————-+——+——+———+——-+
| number  | int(11)     | NO   | PRI | 0       |       |
| fio     | varchar(60) | YES  |     | NULL    |       |
| address | varchar(60) | YES  |     | NULL    |       |
| work    | varchar(60) | YES  |     | NULL    |       |
+———+————-+——+——+———+——-+
4 rows in set (0.00 sec)

mysql> DESCRIBE lr1_2;
+————+————-+——+——+———+——-+
| Field      | Type        | Null | Key | Default | Extra |
+————+————-+——+——+———+——-+
| code       | int(11)     | NO   | PRI | 0       |       |
| name       | varchar(60) | YES  |     | NULL    |       |
| hours      | int(11)     | YES  |     | NULL    |       |
| controle   | varchar(60) | YES  |     | NULL    |       |
| categories | varchar(60) | YES  |     | NULL    |       |
+————+————-+——+——+———+——-+
5 rows in set (0.00 sec)

mysql> DESCRIBE lr1_3;
+———-+————-+——+——+———+——-+
| Field    | Type        | Null | Key | Default | Extra |
+———-+————-+——+——+———+——-+
| code     | int(11)     | NO   | PRI | 0       |       |
| name     | varchar(60) | YES  |     | NULL    |       |
| idZavCaf | int(11)     | YES  |     | NULL    |       |
+———-+————-+——+——+———+——-+
3 rows in set (0.00 sec)

mysql> DESCRIBE lr1_4;
+———-+———+——+——+———+——-+
| Field    | Type    | Null | Key | Default | Extra |
+———-+———+——+——+———+——-+
| auditory | char(5) | NO   | PRI |         |       |
+———-+———+——+——+———+——-+
1 row in set (0.00 sec)

mysql> DESCRIBE lessons;
+———-+———+——+——+———+——-+
| Field    | Type    | Null | Key | Default | Extra |
+———-+———+——+——+———+——-+
| teacher  | int(11) | NO   | PRI | 0       |       |
| lesson   | int(11) | NO   | PRI | 0       |       |
| auditory | char(5) | NO   | PRI |         |       |
| cafedry  | int(11) | NO   | PRI | 0       |       |
+———-+———+——+——+———+——-+
4 rows in set (0.00 sec)

Задание лабораторной: Создать связанные таблицы и получить их отношение.

В чем может быть проблема?
Заранее спасибо.

Понравилась статья? Поделить с друзьями:

Читайте также:

  • Error code 1066 mysql
  • Error code 1064 sql
  • Error code 1064 mysql workbench что это
  • Error code 1063 postgresql
  • Error code 1063 java

  • 0 0 голоса
    Рейтинг статьи
    Подписаться
    Уведомить о
    guest

    0 комментариев
    Старые
    Новые Популярные
    Межтекстовые Отзывы
    Посмотреть все комментарии