SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2001
    Location
    cheshire
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How do you join tables?

    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.

  2. #2
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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)
    Last edited by freakysid; Sep 8, 2001 at 06:38.

  3. #3
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •