heshan Posted September 16, 2012 Share Posted September 16, 2012 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" Quote Link to comment Share on other sites More sharing options...
requinix Posted September 16, 2012 Share Posted September 16, 2012 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... Quote Link to comment Share on other sites More sharing options...
Christian F. Posted September 16, 2012 Share Posted September 16, 2012 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. Quote Link to comment Share on other sites More sharing options...
heshan Posted September 16, 2012 Author Share Posted September 16, 2012 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 Quote Link to comment Share on other sites More sharing options...
Jessica Posted September 16, 2012 Share Posted September 16, 2012 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. Quote Link to comment Share on other sites More sharing options...
heshan Posted September 16, 2012 Author Share Posted September 16, 2012 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'") Quote Link to comment Share on other sites More sharing options...
heshan Posted September 16, 2012 Author Share Posted September 16, 2012 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.. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.