selecting reacords from multiple tables

[ferhanz]

i have following tables in the DB

student, subjects, category, majors, interest

student table has a primary key of student_id and every other table has a foriegn key of student_id ...now on a page i want to display all records from tables having foriegn keys where the student_id is let say 1

plz help me out

[ymasood]

Hi Ferhan,

(If I got you right...)

This would do it on a basic level:

SELECT * FROM subjects, category, majors, interest WHERE student_id = 1

However, you might want to input specific column names instead of * to get a more refined recordset.

Regards,

YM

[ferhanz]

thanks ymasood for the reply, i thought joins would be used to achieve that as the table types are MyIsam?

[ergophobe]

Table types make no difference. You must always use a join to get data out of multiple tables. The thing to understand, though, is that what ymasood gave you is a join. As a join, though, it has a couple of issues

1. it should fail because student_id in the WHERE clause is ambiguous.

2. If you make it unambiguous, you'll end up with a Cartesian join. You need

WHERE subjects.student_id='1' AND category.student_id='1' etc.

OR

WHERE subject.student_id = category.student_id AND subject.student_id = majors.student_id ... etc ... AND subject.student_id = '1'

OR

SELECT * FROM subjects
INNER JOIN category ON category.student_id= subjects.student_id
INNER JOIN majors ON majors.student_id= subjects.student_id
etc
WHERE category.student_id = '1'

These are all joins, and if my brain is working this morning (big IF), they should all yield the same result set.

[ferhanz]

someone one suggest me to use UNION ALL to achive this, any comments in that?

and wht's the difference between UNION and UNION ALL

[arran]

MySQL Reference Manual [dev.mysql.com] is a useful place to start for questions like this.

[dev.mysql.com ]