Possible solution to a simple newbie problem of JOINs

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux