CODEWITHSUNDEEP
mysql
Tim
Tim

Reputation: 1695

mysql - select data from multiple tables with different structures

I have an inventory database where every item ID comes from a single source. Presently the primary table (cp_partlist) looks something like:

- PK_PartID
- TableName

I then have separate tables for each type of item, and each table has different data. For example, I might have a table for bolts which has:

- FK_PartID
- ThreadType
- Length
- DriveType

and a table for washers which has:

- FK_PartID
- OD
- ID
- Thickness

If I want the specific info for a given PartID, right now I am looking up the table name in the primary table, and then using that to get the specific data that I want from the correct table.

It seems like there should be a more direct approach. I tried using a LEFT JOIN like:

    SELECT * FROM cp_partlist AS pl
      LEFT JOIN bolts ON pl.PK_PartID=bolts.FK_PartID
      LEFT JOIN washers ON pl.PK_PartID=washers.FK_PartID
    WHERE PK_PartID=400004

This works except that I end up with a column from every table, not just the table I am interested in. I can't specify the column name because it is different for each table.

Is there a better way to do this? Thanks.

Upvotes: 1

Views: 925

Answers (1)

Simon
Simon

Reputation: 9425

I fi understand you question correctly, you wish to retreive data from your cp_partlist table joined with bolts and washers tables, however you wish to show only four columns, not eight as you are currently getting.

This can be acheived with a UNION statement, however the field types for each of the JOINed columns MUST be of the same type.

So a query like this can be used (if the above criteria is met)

SELECT pl.*, bolts.FKPartID,bolts.ThreadType AS Data1,bolts.Length AS Data2, bolts.Drivetype as Data3  FROM cp_partlist AS pl 
LEFT JOIN bolts ON pl.PK_PartID=bolts.FK_PartID 
WHERE PK.PartID=400004

    UNION

SELECT pl.*,washers.FKPartID,washers.OD AS Data1,washers.ID as Data2, Washers.thickness as Data3 FROM cp_partlist as pl 
LEFT JOIN washers ON pl.PK_partID = washers.FK_partID 
WHERE PK.PartID=400004

Note that this will result in a table with the following columns:

PK_PartID,Tablename,FK_PartID,Data1,Data2,Data3

Upvotes: 0

Related Questions