{"id":463,"date":"2014-10-07T10:35:47","date_gmt":"2014-10-07T17:35:47","guid":{"rendered":"https:\/\/blogs.ubc.ca\/coetoolbox\/?page_id=463"},"modified":"2016-10-19T17:53:52","modified_gmt":"2016-10-20T00:53:52","slug":"querying-multiple-tables","status":"publish","type":"page","link":"https:\/\/blogs.ubc.ca\/coetoolbox\/databases\/querying-multiple-tables\/","title":{"rendered":"Querying multiple tables"},"content":{"rendered":"<p>Most queries will involve working with multiple tables. To combine all values from one table with all values from another table, you can use a cross join: simply selecting the relevant fields from the two tables.<\/p>\n<p>SELECT c.CourseID, c.Term, s.StudentID, s.Year<br \/>\nFROM Courses as c, Students as s<br \/>\nWHERE s.Year=2016;<\/p>\n<p>Unless there are only a few values in the two tables, this is usually a terrible idea, if only because the result set could be huge. It&#8217;s usually safer to use join clauses.<\/p>\n<p><strong>Join Clauses<\/strong><\/p>\n<p>Join clauses are used to combine two tables based on a common field between them. <span style=\"color: #0000ff;\">INNER JOIN<\/span> is the most common type, returns matching entries available in the two tables.<\/p>\n<p><a href=\"http:\/\/www.w3schools.com\/sql\/sql_join_inner.asp\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/www.w3schools.com\/sql\/img_innerjoin.gif\" alt=\"\" width=\"200\" height=\"145\" \/><\/a>For instance, this query will return entries from the students and registrations tables that match based on the student ID:<\/p>\n<p style=\"padding-left: 30px;\">SELECT *<br \/>\nFROM Students S <span style=\"color: #800000;\">INNER JOIN<\/span> Registrations R<br \/>\n<span style=\"color: #993300;\">ON<\/span> S.StudentID = R.StudentID<\/p>\n<p><span style=\"color: #0000ff;\">LEFT JOIN<\/span> and <span style=\"color: #0000ff;\">RIGHT JOIN<\/span> allow you to keep all the entries of one of the two joining tables. Entries with no match will have null values in the corresponding fields.<\/p>\n<table cellspacing=\"0\" cellpadding=\"0\">\n<tbody>\n<tr>\n<td style=\"text-align: center;\"><a href=\"http:\/\/www.w3schools.com\/sql\/sql_join_left.asp\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/www.w3schools.com\/sql\/img_leftjoin.gif\" alt=\"\" width=\"200\" height=\"145\" \/><\/a><\/td>\n<td style=\"text-align: center;\"><a href=\"http:\/\/www.w3schools.com\/sql\/sql_join_right.asp\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/www.w3schools.com\/sql\/img_rightjoin.gif\" alt=\"\" width=\"200\" height=\"145\" \/><\/a><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>For instance this query will match students to the registered courses.\u00a0 Students with no registered courses will have a null value in the field CourseID and will be returned in the result set.<\/p>\n<p style=\"padding-left: 30px;\">SELECT S.StudentName<br \/>\nFROM Students S <span style=\"color: #800000;\">LEFT JOIN<\/span> Registrations R<br \/>\n<span style=\"color: #993300;\">ON<\/span> S.StudentID = R.StudentID<br \/>\nWHERE R.CourseID <span style=\"color: #800000;\">IS NULL<\/span><\/p>\n<p>To cross more than two tables you need to use multiple joins in sequence. Using parentheses helps to avoid errors.<\/p>\n<p style=\"padding-left: 30px;\">SELECT S.StudentName, R.CourseID, C.CourseName<br \/>\nFROM ((Students S <span style=\"color: #800000;\">INNER JOIN<\/span> Registrations R<br \/>\nON S.StudentID = R.StudentID)<br \/>\n<span style=\"color: #800000;\">INNER JOIN<\/span> Courses C<br \/>\nON R.CourseID = C.CourseID)<\/p>\n<p>The <span style=\"color: #0000ff;\">FULL OUTER JOIN<\/span> combines the result of a left and right joins. MS Access doesn&#8217;t has this clause available.<\/p>\n<p>You can find other examples with JOIN clauses <a href=\"http:\/\/www.w3schools.com\/sql\/sql_join.asp\">here<\/a> and a discussion of the types available in MS Access <a href=\"https:\/\/support.office.com\/en-us\/article\/Join-tables-and-queries-3f5838bd-24a0-4832-9bc1-07061a1478f6\">here<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Most queries will involve working with multiple tables. To combine all values from one table with all values from another table, you can use a cross join: simply selecting the relevant fields from the two tables. SELECT c.CourseID, c.Term, s.StudentID, s.Year FROM Courses as c, Students as s WHERE s.Year=2016; Unless there are only a [&hellip;]<\/p>\n","protected":false},"author":22982,"featured_media":0,"parent":74,"menu_order":4,"comment_status":"open","ping_status":"closed","template":"page-templates\/full-width.php","meta":{"footnotes":""},"class_list":["post-463","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages\/463","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/users\/22982"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/comments?post=463"}],"version-history":[{"count":12,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages\/463\/revisions"}],"predecessor-version":[{"id":1535,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages\/463\/revisions\/1535"}],"up":[{"embeddable":true,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages\/74"}],"wp:attachment":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/media?parent=463"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}