Jump to content

Need to join 3 tables


heshan

Recommended Posts

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"

Link to comment
https://forums.phpfreaks.com/topic/268426-need-to-join-3-tables/
Share on other sites

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.

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

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'")

 

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.