> I think part of the confusion stems from the dynamic > tables I was creating with Dreamweaver. I thought they > were a necessary part of the equation, when they may > in fact be optional.
Do you mean temporary tables? These are only necessary when there's no way to solve the problem with a join. > At any rate, I wondered if anyone on this list would > be willing to translate the code I've appended into > two joins that I can use as working examples. In your example you've seperated the properties of the continents from the properties of nations. You could have stored the data on the appropriate continent with each and every nation, but this would mean that you store a lot of data more than once. This leads to storing more data than you actually need to store and it leads to more problems with updating the data. So what you did was the most sensible thing; you stored the data about continents seperated from the data about nations. If you want to show the data of the continent a nation belongs to you need to JOIN the data (and thus JOIN the tables). Basically there are two types of JOINs: - INNER JOINs: you only want to show records from the first table if a related record in the second table exists. In your case: you only want to show nations which have been connected to a continent. Syntax: SELECT * FROM table1, table2 WHERE table1.col1=table2.col3 AND .... or SELECT * FROM table1 JOIN table2 ON table1.col1=table2.col3 WHERE.... or SELECT * FROM table1 JOIN table2 USING (col1) WHERE ... or several other combinations and variations. I prefer version 2 and 3 because they show the relationship in a very clear way. -OUTER JOINs: you want all the data from one table plus the related data from the second table, but if there is no related data from the second table the fields should be left empty (actually they get a NULL value). In your case: you want to show the nations plus the corresponding continent data if it happens to be present. Syntax: SELECT * FROM table1 LEFT JOIN table2 ON table1.col1 = table2.col3 WHERE... or several other variations For the right syntax details see: http://dev.mysql.com/doc/mysql/en/JOIN.html > TABLE CONTINENTS Name, Type, Group, CCode > > TABLE NATIONS Name, Type, NCode, CCode > > 1. A table displaying all four columns from Table > Nations, plus a 5th column - Table Continent's Group > column. Let's assume that you also want the data on nations that haven't been assigned to a continent. SELECT nations.*, continents.Group <--- which columns you want to display FROM nations <--- which table the data comes from LEFT JOIN continents <--- join type and other table the data comes from USING (CCode) <--- join condition > 2. A table displaying both tables side by side - 8 > columns, beginning with Table Continent's Name column > and ending with Table Nation's CCode column. Let's assume that you want to show the data on all nations, including those which are not connected to a continent. This would be almost the same query as 1.: SELECT continents.*, nations.* FROM nations LEFT JOIN continents ON (nations.CCode=continents.CCode) (I used the ON join condition to show a variation) If you want to list all the continents plus data about one nation on each continent: SELECT continents.*, nations.* FROM continents INNER JOIN nations ON (nations.CCode=continents.CCode) If you need to know how to display the resulting record sets, example 1 on: http://www.php.net/manual/en/ref.mysql.php gives you a complete piece of code to print out the resulting records. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
