My newbie problem is solved and it may help other newbies if I share it.
The idea is to take two or more tables and combine their data into one table that you can query. Each of the source tables may only have one or two columns of overlap. You want records (rows) that do not fill in data in unused fields, as JOINs do. (I tried JOINS and tried to get help on JOINS from these lists, but that's another story.*) What worked for me is: Create Table A and Table B, the source tables. Create Table C (Combination table) which will be used to collect the combined data. You can kill the records in this table regularly, as it is only a temporary storage area. Take Table A and INSERT SELECT it into the Combination table. Then take the Table B and INSERT SELECT it into the Combination table. In each of these passes, you can position the data into the columns that you decide, using the Select portion of the statement. The only requirement is that the data type be the same, so you must put an integer into an integer column, text into a text column, e.g. Also, I first use the TRUNCATE statement to kill all records in the Combination table before I load the data into it from Table A and Table B. That way, the Combination table is rebuilt with fresh data just before it is used. My problem was that I needed a database to keep track of a symposium's schedule, which includes about 21 Sessions, each of which has perhaps 10 Talks. It was necessary to make a separate table each for Sessions and Talks because I needed to provide my administrative client with the ability to change a Session title but not have it affect the Talks table, or vice versa. But, when all is said and done, the symposium's web page must deliver a schedule of a day's events that shows the Session title, then the Talks under it, then the next Session title, etc. First Table Name: Sessions ID int(6) autoincrement not null Session varchar(36) null SessionTitle varchar(255) null Sample rows: 1 Session I Dead Poets 2 Session II Live Poets 3 Session III Bad Poets Second Table Name: Talks ID int(6) autoincrement not null Session varchar(36) null StartTime time TalkTitle longtext null Sample rows: 1 Session I 07:00 Death of Smoochy 2 Session I 07:15 Byron's Secret 3 Session I 07:30 Kipling's Curse 4 Session II 10:00 Simon's Rhymes 5 Session II 10:15 AB Schemes Suppose you now want a table that we will call Combo, which gives you all the rows from Sessions and all the rows from Talks: Table Name: Combo ID int(6) autoincrement not null Session varchar(36) null SessionTitle varchar(255) null StartTime time TalkTitle longtext null Here is the SQL code to use: INSERT INTO Combo (Session, SessionTitle) SELECT Session, SessionTitle FROM Sessions (This loads from the table Sessions, into the appropriate columns of Combo) INSERT INTO Combo (Session, StartTime, TalkTitle) SELECT Session, StartTime, TalkTitle FROM Talks (This loads from the table Talks, into the appropriate columns of Combo) What we have assembled in Combo will look like this: 1 Session I Dead Poets null null 2 Session II Live Poets null null 3 Session III Bad Poets null null 4 Session I null 07:00 Death of Smoochy 5 Session I null 07:15 Byron's Secret 6 Session I null 07:30 Kipling's Curse 7 Session II null 10:00 Simon's Rhymes 8 Session II null 10:15 AB Schemes (Notice that I always use an ID field as a primary key in every table, even though this data doesn't need to be passed into the Combo table. Whenever you rebuild Combo using TRUNCATE, you get new ID numbers for the rows.) I can now query the Combo table and use ORDER BY to sort the rows, getting exactly the results I need. (This is only a dummy set of data. The real symposium is completely different and the tables A, B and Combo all have a lot more fields.) This probably isn't a perfect solution. I will bet that there is a better way to do it and some smart guy can put it into one line of SQL code, but I had to get to the goal line like Clint Eastwood--any which way I could. --Dave Shugarts * If you're like me, you're not trying to be some expert in PHP or MySQL. You more or less have to work with PHP and MySQL because it came with your hosted server. So you read the documentation, which is really poor and has little sympathy for non-programmers. And you look for help on these two lists. People from each list tell you that you are asking the wrong question, whether it's a SQL question that has PHP code, or a PHP question that has SQL code. People on both lists tell you to RTFB. So you spend hours and hours figuring out what they could have just shown in a simple example. I think the really smartest guys know the line from Chaucer, "Gladly would he learn and gladly teach." HTH -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
