How to Join 3 tables in 1 SQL query

a relational database has many related tables inside of it these two tables relate on a location ID field this gives us the ability to take these two tables and put them in the same query using a join we also have the ability to join up to 256 tables in one query we're going to start off by doing a recap of a two table join and then expand it to a three table join we can see that Alex Adams works at location 1 which is Seattle Washington Barry Brown also works in location 1 which is Seattle Washington Leo Sacco works in location 2 which is on 2nd Avenue in Boston this second table is known as a lookup table and it contains more detailed information about that location as referenced by the location ID in the employee table let's again repeat the process of exploring related tables and then joining those two tables and let's use the example with the grant table go select all fields from grant and run it we can see that we have a grant ID a grant name and this third field is the employee ID who procured that grant who is employee 7 well we can query the employee table and we notice the employee table has an employee ID and number 7 is david lauding whose employee - that's Barry Brown now let's put both of these tables in the same from clause like so put the word in or join between the table names let me a leus these tables grant will be aliased as gr employee will be aliased as e/m in the on clause let's specify that both these tables have the M PI D field in common GRE M PI D equals a mm PI D and run it now we see the listing of the grants as well as the names the employees who procured grant let's narrow our field selection list down so we just see the grant name the amount the first name of the employee who found the grant and the last name of the employee who found the grant and what we really have here is something that looks like a single table could we join this result set on another table well what will we join it on David Lonnie I think he's from Seattle what other employees are from Seattle how many of these grants were found from Seattle how many were found from Boston and how many were found from Chicago or Spokane well the location ID field is in the employee table but that's just a number what if we want the city name to appear in our report well what we're going to need to do is take the employee table and join it to the location table the location table is not a part of this query yet but what we can do is make another inner join to the location table and join it on the employees location ID being associated with the locations location ID and when I run it it says I have an ambiguity oh that's because location ID needs to come from a specific table here is the new result set showing the location ID field we also want to include from the location table the city field now we can see exactly which grants were found in which cities if we just wanted to see a city report we could actually get rid of these three fields and just have a listing of the grants and what cities they're from lab 2.2 skill check one show all the city names and rates of pay for each employee in those cities you will need to join the location employee and pay rates table show the city field from the location table include the first name and last name from the employee table and show all fields from the pay rates table when you're done your screen should resemble the figure you see here

Channel: Joes2Pros SQL Trainings

Views: 330,654

Rating: 4.8749218 out of 5

Keywords: Inner, Join, Tables, SQL, Queries, Volume2, DVD, Joes2Pros, educational, Server, software tutorial

Id: G1OLrfjHDyw

Length: 4min 59sec (299 seconds)

Published: Mon Jan 02 2012