Unexpected error MySQL Query

Question

I have three tables:

STUDENT table:

create table student
(
    sid int auto_increment primary key, 
    fname varchar(30),
    lname varchar(30)
);

COURSE table:

create table course
(
    cid int auto_increment primary key, 
    ctype text,
    cclass text,
    cduration int,
    certification int,
    cteacher text,
    standard_course_fee int,
);

STUDENT_PAYMENT table:

create table student_payment
(
    transaction_id int auto_increment primary key,  
    sid int,
    cid int,
    paidamount int,
    paiddate date,
    paymentdue int,
    FOREIGN KEY (sid) REFERENCES student(sid),
    FOREIGN KEY (cid) REFERENCES course(cid)
);

I wrote this query:

select 
    sid, fname, lname, cid, ctype, cclass, paidamount, paiddate, paymentdue 
from 
    student, student_payment, course 
where 
    course.cid = student_payment.cid and 
    student.sid = student_payment.sid and 
    sid = 1;

To get expect output table like this:

|sid| fname | lname | ctype | cclass | paidamount | paiddate | paymentdue |
---------------------------------------------------------------------------

but I get an error:

Column sid in field list is ambiguous

Please someone correct my query.

Answer 1

You need to add alise as below. Also, use the join instead of adding all the tables in FROM

    select student.sid,fname,lname,course.cid,ctype,cclass,paidamount,paiddate,paymentdue 
    from student
    inner join student_payment on student.sid=student_payment.sid
    inner join course on course.cid=student_payment.cid
    where student.sid=1;