Multi-Table Key search

indego · #1 (**permalink**) 01-30-04, 10:59

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.

Problem:
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.

eg.
EVENT TABLE
event, contact
1 , 532
2 , 200
3 , 532

BUSINESS TABLE
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;

OR

SELECT
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.

Indego

stacey_richards · #2 (**permalink**) 01-30-04, 14:26

This is not exactly what you want, but may get you on the way:

Code:

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
  person.id as person_id,
  person.name as person_name,
  event.id as event_id,
  event.name as event_name,
  business.id as business_id,
  business.name as business_name
from
  person left join
  event on person.id  = event.contact_id left join
  business on person.id = business.contact_id;

indego · #3 (**permalink**) 02-01-04, 07:14

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.

Indego

Internet Services

Web Development

Digital Marketing

Consumer Tech