How do you join tables?

[p0c79]

I would like to know how to write a query that will pull data from two tables. The first table called 'personal details' has a column called 'userid' which is the same column in the 'testscores' table - in this table it is called 'id'

I want the 'firstname' and 'surname' from the 'personaldetails' table and the 'testscore' from the 'testscores' table.

[freakysid]

The type of join you want is actually called an inner join. If you do a search of this forum on the words "join" and "cartesian" and "product" you will find some threads from the past where I have tried to explain a little of the theory of creating table joins in SQL. It might help - but it might not

So you have the tables:

personal_details(userid, firstname, surname)
testscores(id, testscore)

There might be other attributes in each table - but these are the ones you want to query.

SELECT firstname, surname, testscore
FROM personal_details, testscores
WHERE userid = id

That will give you all the firtsname and surname details that match each tescore. So if Marry Poppins has two corresponding testscores records (related by id) then there will be two rows with Mary Poppins in the result set.

You should grab yourself a good book on SQL and read through it - or look through the links in my signature. A lot of people don't realise the power of SQL to manipulate data into result sets and end up writing complicated php code to do what could have been done with the one query. For example, lets say you want to find out some of the following:

a) The total number of testscore records per person:

SELECT firstname, surname, COUNT(*) AS num_tests
FROM personal_details, testscores
WHERE userid = id
GROUP BY id

b) The average of all the test scores

SELECT AVG(testscoure) AS average_score
FROM testscores

c) The average mark for each person

SELECT firstname, surname, AVG(testscore) AS av_score
FROM personal_details, testscores
WHERE userid = id
GROUP BY id

d) The person with the highest score

SELECT firstname, surname testscore
FROM personal_details, testscores
WHERE userid = id
AND testscore = MAX(testscore)

[seanf]

Originally posted by freakysid
personal_details(userid, firstname, surname)
testscores(id, testscore)

Are you going to start drawing entity relationship diagrams as well Sid

Sean