Querying three tables - JOIN? SELECT?

[medar]

Here is my setup (table in caps, fields in lowercase):

PERSON
auto1 (autonumber field)
name1 (name of person)

LOCATION
auto2 (autonumber field)
loc (city visited)

LINK
auto3 (autonumber field)
link_auto1 (person unique autonum from above)
link_auto2 (city unique autonum from above)
numvisits (how many visits unique person made to unique city)

I have people visiting cities. The same person can go to different cities, or the same one multiple times. AND different people can go to cities others have been to, hence the odd table setup.

Now I have a page where the people's names are listed - when I click on a name I need to be able to display a new page - one that shows the NAME of the person, and all the cities that person has visited. This will involve ALL three tables. I have the link working (passing the autonum in the A HREF tag), but cannot get the new page to display correctly.

I know this is a simple query, but cannot get it to function. I have it setup as a SELECT query - should I be doing a JOIN or something?

Any help would be appreciated.

[rrijnders]

Your DB structure is not "odd", it is exactly what you need to store the info you have. The elegance of the structure is evident in that you can get exactly what you need with one query:

SELECT * FROM PERSON,LINK,LOCATION WHERE (auto1=$selectedPersonID) AND ((auto1=link_auto1) AND (link_auto2=auto2))

This will give you all of a person's visited cities like so:

Bob, New York, 3
Bob, LA, 1
Bob, Chicago, 4

All of the "auto" fields will also be in each returned row if you need them.

-- HTH
-- Rich

[Anon]

Additionally, you might rethink your names on those "auto#" fields.

Try something more descriptive like:
person_id
link_id
location_id

[medar]

Thanks Richard - worked like a charm. Don't know what I had wrong...but I would have sworn I tried that before!

DLB - this was for example only, the actual DB is much more extensive and properly named 🙂