Hi!

This one's for the database buffs around here.

Consider these

tblCountries has all the names of the countries, someone lives in a country. Database normalisation dictates that data should NOT repeat itself, hence the need for a table where countries are listed, and a link (tblCountries.ID - tblPeople.countryid). There are no database links, the link is fictive. I do not like to make links the way access did, that's too rigid...I let the software/SQL handle that.

How to I list the country (among other fields) of the people from the countryid?

I understand this could be a join, but joins have always been illusive to me.

Thanks for shedding some light. Of course, if there's a link to a good tutorial, I'll have learned to fish, and I'll eat for the rest of my life

Thor

I use w3schools when I forget stuff - http://www.w3schools.com/sql/sql_join.asp

1 members found this post helpful.

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.

Be aware 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.")

2 members found this post helpful.

Another classic link: http://sqlzoo.net/3b.htm

[edit]Awwww shucks ... sundial beat me w/ a direct example, rather than a link ;}[/edit]


Cheers,
Tink

1 members found this post helpful.

Thanks for the input...and the bookmarks, they're added, of course...

Off to the phpAdmin to try this out.

Thor

Writing and teaching two semesters of community college classes on databases makes you pretty good at extemporaneous coding.

Did you also teach a thesaurus class as well or just a fan of Roxanne ... hehe