Complex Table Join Request


#1

I know table joins are supposed to be simple, and I’ve kinda sorta got a few of the simplest joins to work, but they’re extremely flawed. I’m not sure what I’m doing wrong, but 1) I think I picked up a bad habit somewhere along the line, and 2) it would be much more helpful if the examples in tutorials were put in context.

For example, this is cool…

SELECT * FROM a,b;

but where do you put it? Like this?

SELECT * FROM a,b;

Or this?

SELECT * FROM a,b;

And is it enclosed in something, like this?:

“SELECT * FROM a,b;”

It would help me tremendously if I had a working model, and it would be even better if it was a fairly complex join. I could then pick it apart to make variations and simpler joins. Accordingly, I’d like to ask if anyone would be willing to write the code for a join with the following specs:

First, I have two tables, named Continents and Nations, with five fields and four fields, respectively. Below are the names of the rows, with TWO entry examples (Africa and the Pacific Ocean) for each column.

Table CONTINENTS
NameC (Africa, Pacific Ocean)
TypeC (continent, ocean)
Group ( Old World, Oceanus)
Hemisphere (South, both)
CCode (caf, opa)

Table NATIONS
NameN (Kenya French Polynesia)
TypeN (nation, dependency)
NCode (ken, fp)
CCode (caf, opa)

CCode (abbreviations for continents and oceans) is designated the foreign key in Table Nations.

Now, suppose I want to to make a table that displays:

  1. The first 3 columns from the table named “continents” (NameC, TypeC Group)

  2. The first 3 columns from the table named “nations” (NameN, TypeN & NCode)

  3. But only the rows that represent jurisdictions in Africa (CCode “caf”) and the Pacific Ocean (CCode “opa”) and…

  4. Only the Pacific Ocean jurisdictions that are NATIONS (not dependencies, territories, or any other designation).

To put it another way, it will list all the African jurisdictions (all of which are nations) and all the Pacific Ocean NATIONS (nothing else), and it will list the first three columns from each table.

This is the connection information from the top of my page:

[php]

<?php require_once('../../../Connections/World.php'); ?> <?php mysql_select_db($database_World, $World); [/php] If you're willing to tackle this, could you post the complete code, from the top of the page to ? I won't even show you what I've got, because I'd like to start with a blank slate and come up with the simplest formula. Thanks!

#2

Hey didn’t I help you with this on the MySQL mailing list??? Ahhh that’s right it was for the foreign keys…

Ok time for some information to get you goin’. Once you have that I think you’ll be OK.

Joins… First place whoever told you joins were simple lied! I think you need to go is here ( http://sqlcourse2.com/joins.html ) it does a fairly decent job of explaining them. Joins are just another SELECT statement and can be used anywhere you would put a normal query in your PHP.

When doing a join you need some information…

  1. what info do you want to come out (SELECT …)
  2. which tables are you using (FROM …)
  3. what conditions must be met (WHERE table1.key1 = table2.key2 AND table2.column3 > 0)

try it yourself and see what you can get. If it doesn’t work show us what you have and we can give suggestions.

as for the database stuff you might wish to look at http://www.codewalkers.com in the tutorials/basics section there is an Excellent tutorial for working with databases called “Creating Dynamic Websites with PHP and MySQL”. Just skip past the installation of the software and the creation of a database to - where’s my view-. from here you learn how to connect and access the information in the database created just previous to that page. It covers a lot of database basics for PHP so you should definately take a look at all of it from that point on. I think that should get you started.

Sorry but I don’t write other peoples code… I will however help you write your own (give a man a fish kind of thing). Let us know if you don’t understand anything you find in the sites I just gave you so we can keep you going in the right direction.

Good luck and you will find it isn’t as hard as you think it is.