joining tables with other tables

[dinsdale]

hello all, one thing ive had alot of trouble with is associating tables with other tables,

example

I make 3 tables right,

 

create table Scientists{

cod varchar(,

name varchar(255)

};

 

create table associated{

scientist varchar(,

project varchar(4)

};

 

create table project{

ID varchar(4),

namepro varchar(50),

hours integer

};

 

Not sure how to put the keys

 

now how do i do a query say,

Show all the scientists (thier name), what project they are doing and the hours of the project

 

thanks

[Yesideez]

First we need a way of linking all the tables with each other.

 

In your table "Scientists" I can't see a numerical primary key, something like this would be handy:

id INT UNSIGNED PRIMARY_KEY AUTO_INCREMENT

 

Then in "associated" we need to somehow link this to Scientists so we can add a field called scientistid in there

scientistid INT UNSIGNED

 

Then with the "project" table we need to somehow link it to the scientist - again, add the scientistid as used above.

 

Now we need to decide which way to join them in a query - will there always be data in associated and project for a scientist or is the data optional?

[dinsdale]

Yes associated needs data

[Yesideez]

I've just created these tables on my server so I can present you with some actual code...

 

CREATE TABLE `scientists` (
`id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 255 ) NOT NULL
) ENGINE = InnoDB

 

CREATE TABLE `project` (
`id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`scientistid` SMALLINT UNSIGNED NOT NULL DEFAULT '0',
`projectname` VARCHAR( 255 ) NOT NULL ,
`hours` SMALLINT UNSIGNED NOT NULL DEFAULT '0'
) ENGINE = InnoDB

 

Let's say you've got a lot of scientists listed and not all of them have a project assigned to them:

SELECT s.*,p.* FROM scientists AS s
LEFT JOIN project AS p ON s.id=p.scientistid
WHERE s.id=$id

 

That will pull all information from the database (both tables) where a scientist exists with the id of $id and if they are associated to a project that information will be shown as well.

 

I don't think there's any need to link with a third table.

[dinsdale]

Thanks for your effort,

"I don't think there's any need to link with a third table."

I always tought that aswell but the problem is.. thats how my teacher wants it done.

the tables i wrote here, come from the exercise sheet.

 

and thus, creates alot of confusion for me hehe

[Yesideez]

Example data for scientists:

id,name
1, Adam
2, Brian
3, Charlie
4, Dave
5, Edward

 

Example data for project:

id, scientistid, project, hours
1, 1, Splitting the Atom, 5
2, 1, Making Gold, 10
3, 2, Invisility, 4

 

Let's pull out Adam's work:

SELECT s.*,p.* FROM scientists AS s
LEFT JOIN project AS p ON s.id=p.scientistid
WHERE s.id=1

Will return:

id 	name 	id 	scientistid 	projectname 	hours
1 	Adam 	1 	1 	Splitting the Atom 	5
1 	Adam 	2 	1 	Making Gold 	10

 

Let's pull out Charlie's work:

SELECT s.*,p.* FROM scientists AS s
LEFT JOIN project AS p ON s.id=p.scientistid
WHERE s.id=3

Will return:

id 	name 	id 	scientistid 	projectname 	hours
3 	Charlie 	NULL 	NULL 	NULL 	NULL

 

Because Charlie doesn't have any projects in the database all we can get is his unique ID number and his name - the other fields are returned as NULL (aka empty)

[Yesideez]

Thanks for your effort,

"I don't think there's any need to link with a third table."

I always tought that aswell but the problem is.. thats how my teacher wants it done.

the tables i wrote here, come from the exercise sheet.

 

and thus, creates alot of confusion for me hehe

We need to make that third table a link table then linking the two. Can you explain what "cod" is please?

[Yesideez]

I can see project in "associated" is used to link to "project" table.

 

What field in "Scientists" links to which field in "associated"?

[dinsdale]

Well i guess scientist.cod with associated.scientist

since they both have the same size

[Yesideez]

SELECT s.*,p.* FROM scientists AS s,associated AS a,project AS p
WHERE s.cod=a.scientist AND a.project=p.id AND s.name='Adam'

 

I think that's what you're looking for.

 

EDIT: Added WHERE clause to allow us to pick out a scientist by name.

[dinsdale]

yeah thats what ive been looking for.. thanks a ton mate

[Yesideez]

You're wekcome - hope the "LEFT JOIN tutorial/guide" there helps

[Yesideez]

btw, the type of join we've finally ended up with is an OUTER JOIN.

[dinsdale]

Ah thanks, ill have a look at a few tuts