Need to join 3 tables

[heshan]

Hi All,

 

I want something like this to be done. When a user enters the admission number and click on "Generate" button the details of the relevant student should come. I want Student Name, Date and state to be displayed. But these fields are in 3 different tables. Therefore i want to join 3 tables.  Can  anyone help me regarding below query??

 

The table structure is as follows.

 

attendance (admission_no, state_id, date)

attendance_state (state_id, state)

student_info (admission_no, name_with_initial,_____)

 

"SELECT * FROM attendance a1 INNER JOIN attendance_state a2, student_info.name_with_initial a3 ON a1.state_id=a2.state_id WHERE a1.admission_no=a3.admission_no"

[requinix]

Use a second JOIN right after the first one.

SELECT fields FROM first table INNER JOIN second table ON condition INNER JOIN third table ON condition...

[Christian F.]

A good tip for this is to write out your SQL sentences over multiple lines, which makes them a lot easier to read.

SELECT *
FROM attendance a1
    INNER JOIN attendance_state a2,
    student_info.name_with_initial a3 ON a1.state_id=a2.state_id
WHERE a1.admission_no=a3.admission_no

 

As you can see there's clearly something missing here, as noted by requinix above.

[heshan]

I have created this one based on you'll comemnts..But still not working.

 

SELECT attendance.admission_no, attendance.date, attendance_state.state, student_info.name_with_initial
FROM attendance a1
         INNER JOIN attendance_state a2 ON a1.state_id=a2.state_id
         INNER JOIN student_info a3 ON a1.admission_no=a3.admission_no

[Jessica]

You should use descriptive aliases, a1, a2 and a3 don't mean anything. Either take out the aliases, or use them on the fields once you've made them.

[heshan]

Yeah, exactly..I removed them and run the query..It works perfectly..

 

But small issue. I want to display details relevant to that particular admission number ONLY. But it displays almost every record in the attendance table.

I have inserted a WHERE clause at the end of the line. But it says "Column 'admission_no' in where clause is ambiguous"

 

SELECT attendance_state.state, attendance.admission_no, attendance.date, student_info.name_with_initial 
FROM attendance_state
         INNER JOIN attendance ON attendance_state.state_id=attendance.state_id 
         INNER JOIN student_info ON attendance.admission_no=student_info.admission_no 
WHERE admission_no='$admission_no'")

 

[heshan]

Ohh yes..i found it..it says it does not specifically say which 'admission_no' to use. Therefore i changed it and now ok..Thanks everyone who helped me to solve this thread..