Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2004
    Posts
    6

    Unanswered: Selecting across a many to many relationship

    hi, I'm very new to sql, and figure this should be a common question, but I've had little luck getting anywhere with tutorials etc.

    I've three tables representing a many to many relationship, so let's say (using arbitary names);

    create table people (
    name varchar(70) not null primary key,
    age integer)

    create table tasks (
    job varchar(100) not null primary key,
    pay integer)

    create table many2manylink (
    index_PK integer not null primary key,
    name varchar(70),
    job varchar(100),
    foreign key name references people(name),
    foreign key job reference tasks(job))

    How would I select all the ages of people who are getting paid a certain amount?

    I was trying to hazard a guess by
    select people.age from people inner join many2manylink on many2manylink.name = people name inner join tasks where tasks.pay = 500

    but it wasn't getting me very far, and I was beginning to just take stabs in the dark.

    Can anyone help break through this mental block?

    thanks,
    nik

    ps Also, do the foreign keys play an important part in the selection process, or just to ensure that no unique fields are broken on data entry?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by nik101
    How would I select all the ages of people who are getting paid a certain amount?
    you were quite close!!
    Code:
    select people.name 
         , people.age 
      from tasks
    inner 
      join many2manylink 
        on tasks.job
         = many2manylink.job
    inner 
      join people
        on many2manylink.name
         = people.name
     where tasks.pay = 500
    note i added name to the SELECT list, because a result set of just the ages doesn't seem all that useful

    ps Also, do the foreign keys play an important part in the selection process, or just to ensure that no unique fields are broken on data entry?
    foreign keys ensure that you cannot assign a job to a person that doesn't exist, nor give a person a job that doesn't exist

    note in your many2manylink table, you should remove the index_PK column, and insert at the end:
    Code:
     , primary key (name,job)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2004
    Posts
    6
    Many many thanks! I will really be able to progress now.

    nik

    ps The indenting in your SQL does make it lot clearer to read, I hadn't thought of applying that! Why I dropped my developer hat just because it was SQL I don't know....

Posting Permissions

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