Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004

    Question Unanswered: Multi-Table Key search

    I have been hunting the web for the past hours trying to find some SQL that has a similar function to what I need, but no luck.

    I have a table 'person' that has name, address, etc, type info and I relate to this by the 'personID' key.

    I have many other tables that have a link into this table to reference a 'contact', but the contact, person in the person table, has no link back. It is possible that they could be the contact for many rows in many tables.

    event, contact
    1 , 532
    2 , 200
    3 , 532

    business, contact
    1 , 340
    2 , 532

    etc. up to 5 tables

    Now, when I view the data over contact 532 I have name, address etc but I would like to add a list of all the related events and businesses etc.

    I have tried stuff like:-
    SELECT eventID , businessID
    FROM event, business
    WHERE event.contactID =$personID or business.contactID =$personID;


    case when person.personID = event.contactID then '1'
    when person.personID = business.contactID then '2'
    else '3' end AS type
    FROM person JOIN event ON person.personID = event.contactID, business
    WHERE personID = 532;

    but they all end up empty.

    Where as:-
    mysql> select eventID from event where contactID =532;
    | eventID |
    | 10030 |
    1 row in set (0.00 sec)

    What I would like is something like:-
    eventID | businessID
    5 | 4
    NULL | 8

    So that they could be listed when you view the person's information.

    Any insight would be great, I know that it can be done, just not HOW.


  2. #2
    Join Date
    Aug 2003
    This is not exactly what you want, but may get you on the way:

    create database sample;
    use sample;
    create table person (id int, name varchar(50), address varchar(50));
    insert into person values (532, 'bob', '1 somewhere lane');
    insert into person values (200, 'jim', '2 nowhere place');
    insert into person values (340, 'sam', '3 my street');
    create table event (id int, name varchar(50), contact_id int);
    insert into event values (1, 'one', 532);
    insert into event values (2, 'two', 200);
    insert into event values (3, 'three', 532);
    create table business (id int, name varchar(50), contact_id int);
    insert into business values (1, 'foo', 340);
    insert into business values (2, 'bar', 532);
    select as person_id, as person_name, as event_id, as event_name, as business_id, as business_name
      person left join
      event on  = event.contact_id left join
      business on = business.contact_id;

  3. #3
    Join Date
    Jan 2004


    Hi Stacey,

    I have adapted it to my database and tried it accross 4 tables and it seems to do the trick.

    A BIG Thanks You!!

    When I tried a similar thing, I don't have the code handy, I was just getting syntax errors.

    As a side note, does anyone know of any good online resorces for leaning SQL, not the very basics, but something that might push me beyond single joins but be well explained for a 'beginner' at the 'intermediate' level.

    I might start a new topic on the above.

    Thanks again.


Posting Permissions

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