Joining three tables... getting 100 rows where there should only be six.

[jfarthing]

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.

HELP!

Thanks for reading,
Jim

[pbmods]

Heya, Jim.

Changed thread title to better describe the problem (did you know that threads whose titles do not follow the Posting Guidelines actually get FEWER responses?).

You're joining three tables (well, two in the example you provided), but you're not giving MySQL any criteria for the join, so you are literally seeing every row of every table joined together in every possible combination that fulfills the WHERE clause.

Try this instead:

SELECT
        `Cats`.`Name`,
        `Address`.`Address`,
        `Address`.`City`
    FROM
    (
            `Cats`
        LEFT JOIN
            `Address`
                USING (`Name`)
    )
    WHERE
        `Cats`.`Category` LIKE '%$search%'
 

[jfarthing]

Oops, I left the contact info out of the original query. I figured that one out on my own!

Your suggestion works perfectly.

Thanks for the help!
Jim