Goal: Find records which has associated data in both Table A (Names) and Table B (Phone Numbers)

Difficulty: Easy

Prerequisites: 2 Tables with relational data, Ability to run SQL Queries

Many database designs incorporate relationships between tables. One of the most useful things with the SQL syntax is the ability to link items found in both tables using an INNER JOIN query. Below are my sample tables:

Names

ID  Name
-- ----
0  Joe
1  Chris
2  Mason

Phones

ID  Extension
-- ---------
0  x0001
0  x0101
2  x3121

Now in this example "Joe" (user ID 0) has 2 phone extensions, Chris has no extensions, and Mason has 1 extension. So lets say we want to get all of that in a table so that we can use it later to display it on our webpage. The following query will pull all users who have an extension and their extensions, and leave off any user who does not.

SELECT * 
FROM Names 
    INNER JOIN Phones ON Names.ID=Phones.ID

And the result of that query would look something like this:

ID Name  Extension
-- ----- --------
0  Joe   x0001
0  Joe   x0101
2  Mason x3121

As you can see it left chris out of this altogether because he did not have any matching criteria in both tables for what we did our INNER JOIN on. If we wanted to list chris in this we would simply need to use an outer join instead of an inner join and then in the Phone Extension it would just be null.

Comments

Leave a Reply



(Your email will not be publicly displayed.)



Search