Putting multiple tables into a search

[CountryGirl]

I changed the code a little, since I realized I was being a bit redundant . It is working better than it was, but it is still not returning a few names.

 

This is what I changed it to:

"SELECT asmnt_legal.Account, asmnt_legal.Legal, cmn_name.OwnersName, cmn_name.Address1, cmn_name.Address2, cmn_name.City, cmn_name.State, cmn_name.ZipCode, asmnt_parcel.ParcelID
             FROM asmnt_legal
             LEFT JOIN cmn_name ON asmnt_legal.KeyID=cmn_name.KeyID
             LEFT JOIN asmnt_parcel ON asmnt_legal.KeyID=asmnt_parcel.KeyID
             WHERE asmnt_legal.Account = '{$search}' OR cmn_name.OwnersName = '{$search}' OR   cmn_name.Address2 = '{$search}' OR asmnt_parcel.ParcelID = '{$search}'
             ORDER BY asmnt_legal.Account ASC, asmnt_legal.Legal ASC";

 

The table that contains the names is cmn_name and the field that holds the name is OwnersName.

 

The structure of the 3 tables I'm using above is:

 

asmnt_legal

---------------

KeyID

Account

Legal

 

cmn_name

----------------

KeyID 

NameID

RowID

OwnersName

Address1

Address2

City

State

ZipCode

 

asmnt_parcel

------------------

KeyID

RowID

Account

ParcelID

Township

Range

Section

Quarter

NameID

OwnersName

SubdivisionName

LotNumber

ParentAccount

TotalValue

TaxAreaCode

LotSize

LotSizeType

 

Thanks!

Qadoshyah

[zeodragonzord]

You seem to be joining tables by KeyID of each table.  Are you certain that those should match, that KeyID should be the same always?  When you insert a row in each table, do you track the KeyID so that all should match or are you relying on auto-incrementing primary key IDs?

[CountryGirl]

You seem to be joining tables by KeyID of each table.  Are you certain that those should match, that KeyID should be the same always?  When you insert a row in each table, do you track the KeyID so that all should match or are you relying on auto-incrementing primary key IDs?

 

Well, that's where I'm running into problems I think. I think some of the KeyIDs match, but I don't believe they all do. What I'm confused by is that not every table has the same primary key. Do I have to link them by a primary key or just anything that will make them pull them correct info? The company I'm working for just switched webhosts, so I am going to have to re-do everything anyways (...sigh...). And put everything into a new MySQL database on the new host. So, I may be able to get it better organized this time around.

 

Qadoshyah