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
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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • 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.