Results 1 to 4 of 4

Thread: indexed tables

  1. #1
    Join Date
    Feb 2004
    Posts
    2

    Unanswered: indexed tables

    Hello,

    I hope this is a simple Question.
    I have one person table with ID, name and organisation.

    A second table stores a rowing crew with many members. I will only store the person.ID and in the result I want the names of all crew members in one row

    The crew table looks like:
    boat, men1, men2, men3
    004, 321, 342, 45

    And the result should look like

    boat, men1, men1.name(person), men2, men2.name(person)

    004, 321, houart, 342, smith

    But I have no idea about the query.

    Please help.

  2. #2
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    482

    Re: indexed tables

    Originally posted by Niebarst
    Hello,

    I hope this is a simple Question.
    I have one person table with ID, name and organisation.

    A second table stores a rowing crew with many members. I will only store the person.ID and in the result I want the names of all crew members in one row

    The crew table looks like:
    boat, men1, men2, men3
    004, 321, 342, 45

    And the result should look like

    boat, men1, men1.name(person), men2, men2.name(person)

    004, 321, houart, 342, smith

    But I have no idea about the query.

    Please help.
    Solution is pretty easy. Go to www.google.com and find some SQL tutorial and learn something about JOIN (SELECT) queries.

  3. #3
    Join Date
    Aug 2003
    Posts
    32
    This might get you started:
    Code:
    create database row;
    use row;
    create table person (id int, name varchar(50));
    insert into person values (1, 'bob');
    insert into person values (2, 'sam');
    insert into person values (3, 'jim');
    create table crew (boat_id int, man1_id int, man2_id int, man3_id int);
    insert into crew values (1, 1, 2, 3);
    select
      boat_id,
      man1_id,
      person1.name as man1_name,
      man2_id,
      person2.name as man2_name,
      man3_id,
      person3.name as man3_name
    from
      crew left join
      person person1 on crew.man1_id = person1.id left join
      person person2 on crew.man2_id = person2.id left join
      person person3 on crew.man3_id = person3.id;

  4. #4
    Join Date
    Feb 2004
    Posts
    2

    Cool

    Ohh, thanks Stacey,

    Thats the solution for my problem. I allways tested queries with a single Join, but this ......

    THANKS

Posting Permissions

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