[MySQL] Search for PrimID across multiple tbls

Hi there,
I'v been working with PHP and MySQL for roughly 6 months now and am having trouble with searching our DB. FYI, this is a parts inventory system. There are 8 tables, each containing different types of parts. In each of these tables are fields such as part number, qty, price, etc, etc. The part location is the primary ID, and is a common field to all tables. I have setup a working search within each of these tables(or part category), but not a 'global' search.

This 'global' search is actually a barcode scanner input textbox. I would like to search, across all of the 8 categories[tables] for the Primary ID(location). I have read about using the JOIN statement, but this seems to only be useful for tables which have associated data. Ideally, I could query like: "SELECT * FROM SEMI_Diodes, SEMI_Regulators, Resistors WHERE Location LIKE 'B5-45'" Of course this doesn't work.

Although a bit dirty, I have considered getting a list of all the tables in the DB, then querying each one, until I returned a non-null result set. Any ideas on how to achieve the above simply without getting burried in ugly code?

Any input greatly appreciated,
-Jamie

I suspect that the process of normalisation is news to you.
You should have one table for all parts (this way you also avoid duplicate id's) then the part type should be a field.

Let me have a rough guess here (might not be 100% compliant mysql code).

Next we add our inventory:

Now to your code:


Excuse my bad sql, but I hardly ever work with MySQL.
Had you not been using mysql you cold even have done it with subqueries, but you should probably redesign your database to comply with 3rd level noramlisation to save yourself from grief and headaches later on.