CODEWITHSUNDEEP
phpmysqlinner-join
shasi kanth
shasi kanth

Reputation: 7094

mysql select columns in join query

Scenario:

Students can register in one or more classes.

Structure:

students table has columns: id,firstname, lastname, email, dob, age, gender, mobile, address.

classes table has columns: id,name.

student_classes table has columns: student_id,class_id

Now Iam joining 3 tables: students, classes, student_classes with an inner join query:

select *
from students inner join student_classes 
  on students.id=student_classes.student_id  inner join classes
  on student_classes.class_id=classes.id

But i don't want all the columns from the students table. I just want the firstname. How can i modify the join query?

Upvotes: 3

Views: 6410

Answers (4)

KevinS
KevinS

Reputation: 7875

Try this query:

select students.firstname from ....

Upvotes: 1

Yoram de Langen
Yoram de Langen

Reputation: 5499

You can do it like this:

s  = students table
sc = student_classes
c  = classes

Query:

select 
    sc.student_id,
    s.firstname,
    s.lastname
from students s
inner join student_classes sc on sc.student_id = s.id
inner join classes c on c.id = sc.class_id

Upvotes: 5

thedom
thedom

Reputation: 2518

SELECT students.firstname FROM students
INNER JOIN student_classes ON students.id = student_classes.student_id
INNER JOIN classes ON student_classes.class_id = classes.id

For reference: http://dev.mysql.com/doc/refman/5.1/de/select.html

Upvotes: 2

Dan Grossman
Dan Grossman

Reputation: 52372

Put that column in the SELECT clause instead of *.

SELECT students.firstname FROM ...

If you still want columns from the other tables, you can list them too, or use tablename.*

Upvotes: 2

Related Questions