SELECT DISTINCT co.name as CountryName, pp.id as PersonID
FROM tblPeople pp
INNER JOIN tblCountries co ON (pp.countryID = co.ID)
The concept is simple ... keep
it that way.
When you write a SELECT
, you can draw information from as many tables as you wish. The only thing that you need to do is to specify what the various tables have in common. The database manager will match-up the rows from the various sources, filter it down to only DISTINCT
rows if you wish, then select from the matched-up rows.
that if, say, the "left side" table contains (say) 2 occurrences of a particular value, and the "right side" table contains (say) 3 occurrences, the result-set will contain "2 * 3 = 6" rows having that value. It can add up very fast.
Normally, the join is an "inner" join. That is, it includes only those rows which exist in both
tables. But you can also have "outer" joins, either "left" or "right." A "left" outer-join always includes all of the rows from the table on the "left" side of the join whether or not those rows have matching rows in the table on the "right" side, and vice-versa.
Notice also that it is possible to join tables without
specifying an ON
(or, in mySQL, USING
...) clause, in which case you get a cartesian product
: every row matched with every row. (Not a good thing if both tables contain 50,000 rows each. "You asked for it. You got it. All 2,500,000,000 rows of it. Your system died, of course, but... you asked for it, and you got it.")