How to Join This Tables (3 tables)

[Petite-Dragon]

So i got 3 tables

Table1(instructor)

-id (primary key , ai)

-username

-password

-email

-name

 

 

Table2(i_group)

-id (primary key, ai)

-i_id (this connects to Table1*instructor* row id)

-code (code here comes from Table1*instructor*) instructor can have more than 1 code

 

Table3(student)

-id (primary key, ai)

-username

-password

-name

-group (this connects to Table2*i_group* row code)

 

well i want an output of all rows of Table3(student)  where is separated depends on the instructors id that matches the code.

 

id>id_id and code> group

 

Example 

 

Instructor

id = 1

username = instructor1

password = password

email = email@y.c

name = instructor

 

i_group

id =    1 / 2 / 3

i_id = 1 / 1 / 2

g_code = MYCODE1 / MYCODE2 / MYCODE3

 

student

id = 1 / 2 / 3

username = uname1 / uname2 / uname3

password = pass1 / pass2  / pass3

name = name1 / name2 / name 3

group = MYCODE1 / MYCODE1 / MYCODE 2

 

and when i the input on the instructor is 1

the output of the student must be :

 

id = 1 / 2

username = uname1 / uname2 

password = pass1 / pass2  

name = name1 / name2 

group = MYCODE1 / MYCODE1 

 

 

i know my question is a bit of confusing i hope you guys can understand my question

 

[Barand]

In answer to your question in the title

SELECT 
      i.name as i_name
    , g.group_id
    , g.code
    , s.student_id
    , s.name as st_name
    , s.username as st_uname
    , s.password as st_pwd
FROM student s
    INNER JOIN stud_group g USING (group_id)
    INNER JOIN instructor i USING (instructor_id)
ORDER BY i_name, group_id

hint : avoid "group" as a table or column name - it's a reserved word

[Petite-Dragon]

In answer to your question in the title

SELECT 
      i.name as i_name
    , g.group_id
    , g.code
    , s.student_id
    , s.name as st_name
    , s.username as st_uname
    , s.password as st_pwd
FROM student s
    INNER JOIN stud_group g USING (group_id)
    INNER JOIN instructor i USING (instructor_id)
ORDER BY i_name, group_id

hint : avoid "group" as a table or column name - it's a reserved word

 

do u think i wrote it in the right way?

 

SELECT instructor.name

, i_group.id

, i_group.code

, student.id

, student.name

, student.username

, student.password

FROM student

INNER JOIN i_group.id

INNER JOIN instructor.id

ORDER BY instructor.name, instructor.id

[Petite-Dragon]

 

do u think i wrote it in the right way?

 

SELECT instructor.name

, i_group.id

, i_group.code

, student.id

, student.name

, student.username

, student.password

FROM student

INNER JOIN i_group.id

INNER JOIN instructor.id

ORDER BY instructor.name, instructor.id

 

i should also change my student tablename(group) into other names?

[Barand]

 

do u think i wrote it in the right way?

 

SELECT instructor.name

, i_group.id

, i_group.code

, student.id

, student.name

, student.username

, student.password

FROM student

INNER JOIN i_group.id

INNER JOIN instructor.id

ORDER BY instructor.name, instructor.id

 

You haven't told it which columns to match on when doing the joins so you will get every row in each table joined with every row in the other tables (a cartesian join) so if you have x students, y groups and z instructors your query will return x * y * z rows

[jeffreyappel]

You question is not so clear.Anyway, seems its a simple inner join case.An SQL INNER JOIN return all rows from multiple tables where the join condition is met.

Example:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;

Order:Table 

OrderID   CustomerID   OrderDate

10308       2                 1996-09-18

10309      37                1996-09-19

10310      77                 1996-09-20

 

Customer Table:

 

CustomerID    CustomerName         ContactName                            Country

1                    Alfreds Futterkiste      Maria Anders                           Germany

2                    Ana Trujillo                helados Ana Trujillo                 Mexico

3                    Antonio Moreno        Taquería Antonio                     Mexico