Topic: Can I query by association?

[aeroswat]

Is there a PHP solution I would have to use here or can I do this directly through my query? I have two tables. One holds registration information for students. It has an autonumber that it assigns to each student so in turn they are distinct. Whenever a student places an order the information for the order is placed in my order table. The way that it links the two is it has the student's number on each order record. I want to do searches through the order table but I want to order it by the student's names.

[Mchl]

Code: [Select]

SELECT o.* FROM orderTable AS o INNER JOIN registrationTable AS r ON o.studentNumber = r.studentNumber WHERE r.studentName = ?

[aeroswat]

Code: [Select]

SELECT o.* FROM orderTable AS o INNER JOIN registrationTable AS r ON o.studentNumber = r.studentNumber WHERE r.studentName = ?

So just trying to break that down real fast. I am selecting all the elements on the orderTable that can be accessed by o.columnname where the order student numbers equal the registration student numbers and only grabbing the ones where the student name from the registration equals what I want?

[Mchl]

That's how it should work.

[aeroswat]

That's how it should work.

Thanks

[aeroswat]

Now if I use inner join can i still access my column names from my original table name or do I have to put the table name in front of everything? So for instance I have this

Code: [Select]

SELECT tblOrders.*, tblStudents.StudentName FROM tblOrders INNER JOIN tblStudents ON tblOrders.StudentNumber=tblStudents.StudentNumber WHERE col1='bla' AND col2='bla' ORDER BY tblStudents.StudentName
If col1 and col2 are from tblOrders will I have to put tblOrders in front of them or will it be ok the way it is?

[Mchl]

You can use aliases as I did in my first post

Code: [Select]

SELECT o.*, s.StudentName FROM tblOrders AS o INNER JOIN tblStudents AS s ON o.StudentNumber=s.StudentNumber WHERE s.col1='bla' AND s.col2='bla' ORDER BY s.StudentName

[aeroswat]

You can use aliases as I did in my first post

Code: [Select]

SELECT o.*, s.StudentName FROM tblOrders AS o INNER JOIN tblStudents AS s ON o.StudentNumber=s.StudentNumber WHERE s.col1='bla' AND s.col2='bla' ORDER BY s.StudentName

Alright i will. Thanks again for helping me to understand the query.