Hi everyone,

I am trying to write a query that joins 3 tables and having a
problem.... First, some structure info, the 3 tables are.....

Address
Name
Street
City
State
Zipcode
ID

Contact
Name
Phone
Fax
Email
URL

Keys
Name
Keywords
ID

Now, I get a term from a form and search Keys for matches in Keywords.

I take the Name value and go to the other tables for info based on
that name. For example to display all of the info for 'Antiques' I
use Keys to get the names of all of the antique dealers. I then need
go to Address and get the address info and Contact to get the contact
info.

I tried using
SELECT Cats.Name,Address.Address,Address.City
FROM Cats, Address
where Cats.Category LIKE '%%$search%%'

But that didn't work out. It gave me one name and well over 100
entries when I selected 'accountants' from the form when I know that
there are 6 accountants in the DB.

!

Thanks for reading,
Jim

JimJx wrote:
> Hi everyone,
>
> I am trying to write a query that joins 3 tables and having a
> problem.... First, some structure info, the 3 tables are.....
>
> Address
> Name
> Street
> City
> State
> Zipcode
> ID
>
> Contact
> Name
> Phone
> Fax
> Email
> URL
>
> Keys
> Name
> Keywords
> ID
>
> Now, I get a term from a form and search Keys for matches in Keywords.
>
> I take the Name value and go to the other tables for info based on
> that name. For example to display all of the info for 'Antiques' I
> use Keys to get the names of all of the antique dealers. I then need
> go to Address and get the address info and Contact to get the contact
> info.
>
> I tried using
> SELECT Cats.Name,Address.Address,Address.City
> FROM Cats, Address
> where Cats.Category LIKE '%%$search%%'
>
> But that didn't work out. It gave me one name and well over 100
> entries when I selected 'accountants' from the form when I know that
> there are 6 accountants in the DB.
>
> !
>
> Thanks for reading,
> Jim

The first thing to say is that you should normalize the data. Your current
schema violates the first normal form and that is not good.

Also, in a case like this, Name should not be the foreign key. Two peoplecan
have the same name, yet do totally different things.

Next, you have used a comma join on the tables, but you haven't supplied any
join criteria. So it's not surprising that you got all the stuff out of the
address table.