Three tables, one with foreign keys referencing to other two tables-CodePudding

I have got three tables:

In my project, there are several courses. A user is able to add a course to the dashboard. I am trying to display these courses in the dashboard. In that case, I don't know how to query it. userclasses is a table that contains user and a course user chose.
UPD: userId is a foreign key referencing to users.id and classId is a foreign key referencing to classes.id

CodePudding user response：

To expand upon Roslan's first example, for using JOINs within the FROM clause where the joins should exist, need to JOIN the tables so that you can get the related data based on the foreign keys:

  SELECT uc.id, u.name, u.email, u.password, u.status,
         c.name, c.short_description, c.long_description
    FROM userclasses uc 
      INNER JOIN classes c ON uc.classId = c.id
      INNER JOIN users u ON uc.userId = u.id
ORDER BY uc.id, u.name, c.name;

For some academia, the use of table aliases (such as the uc, u and c in the above example) might not be allowed. In this case, one simply has to specify all table references instead, where there may be ambiguous column names (same name, found in more than 1 table of the combined join) such as id and name:

  SELECT userclasses.id, users.name, email, password, status,
         classes.name, short_description, long_description
    FROM userclasses
      INNER JOIN classes ON userclasses.classId = classes.id
      INNER JOIN users ON userclasses.userId = users.id
ORDER BY userclasses.id, users.name, classes.name;

No worries though, leaning joins was a big hurdle for me way back when I first started learning SQL too, and at a time when comma-separated tables in the FROM clause was more common. To some, foreign key's (FK) are a tough concept to learn as well, but once one realizes that a FK is simply nothing more than a reference to some other table's id, this quickly becomes no big deal.

CodePudding user response：

I think this method is just fine:

SELECT * FROM classes INNER JOIN (users, userclasses) ON (users.id = userclasses.userId and classes.id = userclasses.classId);

CodePudding user response：

You can use joins. For example,

select uc.id, u.name, u.email, u.password, u.status,
       c.name, c.short_description, c.long_description
from userclasses uc, classes c, users u
where uc.classid = c.id and uc.userid = u.id
order by uc.id, u.name, c.name;

OR:

SELECT * FROM userclasses
LEFT JOIN (classes, users)
ON (classes.id = userclasses.classId
    AND users.id = userclasses.userId)