CODEWITHSUNDEEP
phpsqllaravel
Nowshin
Nowshin

Reputation: 91

SQL query SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name

I'm trying to join 3 tables where 1 table has another two table's primary key. The code is given below:

At first,I tried to run the query without using any alias. Then changed it .


     public function member_show()
     {
        $number_of_member =
        DB:: table('members')
        ->distinct('email')
        -> count('email');

        $data = DB::select("
          SELECT m.member_id, m.member_name, m.email, m.password,              
          cl.club_name, ct.city_name, jobtype.type_name 
          FROM members as m
          LEFT JOIN clubs as cl ON cl.club_id = m.club_id 
          LEFT JOIN cities as ct ON ct.city_id = m.city_id 
          LEFT JOIN (SELECT * 
          FROM jobs as jb
          LEFT JOIN j_types as jt
          ON jt.type_id = jb.type_id) as jobtype
          ON jobtype.member_id = m.member_id
         ");

        return view('member.show',['num_member' => $number_of_member, 'data'     
        => $data]);
    }

The error is:

   SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name   
   'type_id' (SQL: SELECT m.member_id, m.member_name, m.email, m.password,
   cl.club_name, ct.city_name, jobtype.type_name FROM members as m LEFT JOIN  
   clubs as cl ON cl.club_id = m.club_id LEFT JOIN cities as ct ON
   ct.city_id = m.city_id LEFT JOIN (SELECT * FROM jobs as jb LEFT JOIN j_types
   as jt ON jt.type_id = jb.type_id) as jobtype ON jobtype.member_id = 
   m.member_id    )

Upvotes: 0

Views: 4617

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

The problem is that the SELECT * in the subquery returns all columns from all tables in the FROM. There is at least one duplicate column name -- type_id used in the ON clause -- but there could be others as well.

The subquery is unnecessary. And, in some databases could impede performance.

Write the query without the unnecessary subquery:

SELECT m.member_id, m.member_name, m.email, m.password,              
      cl.club_name, ct.city_name, jt.type_name 
FROM members m LEFT JOIN
     clubs cl
     ON cl.club_id = m.club_id LEFT JOIN
     cities ct
     ON ct.city_id = m.city_id LEFT JOIN
     jobs jb
     ON jb.member_id = m.member_id LEFT JOIN
     j_types jt
     ON jt.type_id = jb.type_id;

Upvotes: 1

ako
ako

Reputation: 2126

In the inner select type_id exists in both j_types and jobs tables, so you must select one of them.

(SELECT * 
          FROM jobs as jb
          LEFT JOIN j_types as jt
          ON jt.type_id = jb.type_id) as jobtype

Change to something like this:

(SELECT jb.*, jt.x, jt.y, jt.z
          FROM jobs as jb
          LEFT JOIN j_types as jt
          USING (type_id)) as jobtype

Upvotes: 2

Related Questions