Not unique table/alias error while joining 4 tables

I want to run a join query over 4 tables.

The first table is members table, and I need to get these fields:

1	mbr_id Primary	        bigint(20)	AUTO_INCREMENT	
2	mbr_name	            varchar(50)	
3	mbr_family	            varchar(50)
4   mbr_father_name	        varchar(50)	utf8_general_ci	
12	mbr_national_code       varchar(10)	
15	mbr_mobile              varchar(11)	
22	created_at	            timestamp	
23	updated_at	            timestamp

The 2nd one is exam table and I need to get this fields:

1	exm_id Primary	            bigint(20)				
5	exm_candidate_id Index	    int(11)		----> connected to members.mbr_id	
9	exm_correct_answer_count	int(11)			
10	exm_wrong_answer_count	    int(11)			
11	exm_no_answer_count	        int(11)			
12	exm_score	                decimal(8,2)

The 3rd table is students:

1   std_id Primary              bigint(20)  
2   std_mbr_id Index            bigint(20)          ----> connected to members.mbr_id
3   std_degree_id Index         bigint(20)          ----> connected to baseinfos.bas_id 
4   std_grade_id Index          bigint(20)          ----> connected to baseinfos.bas_id
5   std_filed_id Index          bigint(20)          ----> connected to baseinfos.bas_id
6   std_major_id Index          bigint(20)          ----> connected to baseinfos.bas_id
7   std_education_type_id Index bigint(20)          ----> connected to baseinfos.bas_id  
9   std_school                  varchar(190)
10  created_at                  timestamp   
11  updated_at                  timestamp

And this is the table structure of baseinfos:

1   bas_id Primary          bigint(20)      UNSIGNED    
3   bas_value               varchar(100)

So in order to select all those fields, I used this command to select over three tables like this:

SELECT members.mbr_name, members.mbr_family, members.mbr_father_name, members.mbr_national_code, members.mbr_mobile, exam.exm_correct_answer_count, exam.exm_wrong_answer_count, exam.exm_no_answer_count, exam.exm_score, baseinfos.bas_value, students.std_school 
FROM (((((((members INNER JOIN exam ON members.mbr_id = exam.exm_candidate_id AND exam.exm_critical_date_id = 199) 
INNER JOIN students ON exam.exm_candidate_id = students.std_mbr_id) 
LEFT JOIN baseinfos ON students.std_degree_id = baseinfos.bas_id) 
LEFT JOIN baseinfos ON students.std_grade_id = baseinfos.bas_id) 
LEFT JOIN baseinfos ON students.std_filed_id = baseinfos.bas_id) 
LEFT JOIN baseinfos ON students.std_major_id = baseinfos.bas_id) 
LEFT JOIN baseinfos ON students.std_education_type_id = baseinfos.bas_id);

But I get this error:

MySQL said: Documentation

#1066 - Not unique table/alias: 'baseinfos'

So what is going wrong here and how to fix this issue and properly join these 4 tables?

Snapey

4 years ago

Level 122

why do you need all those nested braces?

Tray2

4 years ago

Best Answer

Level 73

You can do something like this

DB::table('books')
  ->join('series', 'books.serie_id', 'series.id') //Here I join another table and specify the keys it should join on
  ->select([ //Here I specify which columns to get
   'books.author',
   'books.author_slug AS something', //Here I give the author_slug column an alias of something.
    'books.title',
    'books.title_slug',
    'series.serie',
     'series.serie_slug',
    'books.part',
    'books.published',
    'series.start_year'
 ])
->get();

Just add additional tables with another join and specify your columns and give them the aliases you need.

Just a tip, never prefix your column names with an acronym of the table name. It will just generate more work, it's better to follow the Laravel naming convention since it gives you a lot of functionality for free.

2 likes

Please or to participate in this conversation.