A Database Error Occurred Error Number: 1066 Not unique table/alias:

Question

Hey I'm building very complex query, I don't know it is calculated as complex or not but since I'm new to SQL this is pretty complex

Error message:

A Database Error Occurred Error Number: 1066 Not unique table/alias: 'bb'

 SELECT cl.*,
            (CASE  
                WHEN id_student IS NOT NULL THEN 'BOOKED' ELSE 
                (
                    CASE 
                    WHEN (max_count - student_count) > 0 THEN 'AVAILABLE' ELSE 'FULL'
                    END
                )
            END) AS course_status        
        FROM (    
                SELECT         aa.*, bb.id_student, cc.max_count, dd.student_count
                FROM        (
                                    SELECT c.id_course, c.id_study, c.id_level, c.id_teacher, c.course_date, 
                                s.study_name,
                                t.teacher_name,
                                t.teacher_picture
                                FROM tbl_course c
                                LEFT JOIN tbl_study s ON s.id_study = c.id_study
                                LEFT JOIN tbl_teacher t ON t.id_teacher = c.id_teacher
                                WHERE
                                CONCAT('"', REPLACE(c.id_level, ',', '","'), '"') LIKE '%"64"%'
                                AND c.id_study = '2'
                                AND c.course_date 
                                AND c.id_study = '2'AND c.course_date 
                                     BETWEEN "2016-10-02 00:00:00" AND "2016-10-09 23:59:59" ORDER BY c.course_date ASC
                                    ) aa
                LEFT JOIN      (
                                        SELECT     id_student, id_course
                                        FROM     tbl_course_student 
                                            WHERE id_student = '305'
                                              AND id_study = '2'
                                    ) bb ON aa.id_course = bb.id_course
                LEFT JOIN        (
                                    SELECT SUM(student_count_max) AS max_count, course_date
                                    FROM tbl_course
                                    WHERE id_study = '2'
                                         AND course_date BETWEEN "2016-10-02 00:00:00" AND "2016-10-09 23:59:59" 
                                              AND id_study = '2') bb ON bb.id_course = aa.id_course 
                LEFT JOIN        (
                                    SELECT SUM(student_count_max) AS max_count, course_date
                                    FROM tbl_course
                                    WHERE id_study = '2'AND course_date BETWEEN "2016-10-02 00:00:00" AND "2016-10-09 23:59:59" 
                                         AND CONCAT('"', REPLACE(id_level, ',', '","'), '"') LIKE '%"64"%'
                                    GROUP BY course_date, id_study
                                ) cc ON aa.course_date = cc.course_date
                LEFT JOIN       (
                                        SELECT SUM(student_count) AS student_count, course_date
                                  FROM      tbl_course 
                                    WHERE id_study = '2'
                                         AND course_date BETWEEN "2016-10-02 00:00:00" AND "2016-10-09 23:59:59" 
                                         AND CONCAT('"', REPLACE(id_level, ',', '","'), '"') LIKE '%"64"%'
                                         AND course_date BETWEEN "2016-10-02 00:00:00" AND "2016-10-09 23:59:59" AND CONCAT('"', REPLACE(id_level, ',', '","'), '"') LIKE '%"64"%'
                                    GROUP BY course_date, id_study
                                     ) dd ON aa.course_date = dd.course_date
        ) cl
        GROUP BY course_date, course_status

My question: Where did I do wrong? I mean, I've been reading those query over and over but yet I found nothing.

score 1 · Accepted Answer · answered Oct 04 '16 at 07:56

1

You have 2 times the alias 'bb'

In your first join:

) bb ON aa.id_course = bb.id_course

and here in your second join:

AND id_study = '2') bb ON bb.id_course = aa.id_course

You need to rename 1 of these to another alias (and map it correctly in your usages offcourse).

answered Oct 04 '16 at 07:56

Blaatpraat

2,829
11
23

thanks a lot, I still need to fix it to make it work – noob e tah Oct 04 '16 at 12:34

score 1 · Answer 2 · answered Oct 04 '16 at 07:57

1

You are using the bb alias for two inner queries. You must not do that. Every alias needs to be unique.

answered Oct 04 '16 at 07:57

Zoli Szabó

4,366
1
13
19

A Database Error Occurred Error Number: 1066 Not unique table/alias:

2 Answers2