Mysql sub queries?

Question

I have a report table that looks similar to this

reports
inspection_type | inspection_number
berries         | 111 
citrus          | 222
grapes          | 333

inspection_type in my case is the name of the other table I would like to SELECT * from where the inspection_number equals report_key on that associated table.

{fruit}
row      | report_key | etc....
value    | 111        | value
value    | 222        | value

The issue is I do not know how to query inspection_type to get the table name to query the value. Does that make any sense?

I tried this here, but even I know that it's glaringly wrong:

SELECT inpection_type, inspection_number
FROM reports rpt
ON rpt.inspection_number = report_key
(SELECT * FROM inspection_type WHERE status < '2')
WHERE rpt.status < '2'
ORDER BY rpt.inspection_number DESC

Could a SQL guru tell me the best way to do this?

Answer 1

You can't really do what you are aiming to in SQL alone, you'll need to either mess around in another language, or (and this is the preferred solution) restructure the database i.e. (sorry for the meta-code)

// Comes in where your existing `reports` table is
inspections (
    inspection_id INT UNSIGNED NOT NULL AI,
    inspection_type_id INT UNSIGNED NOT NULL (links to inspection_types.inspection_type_id)
    .... other rows ....
)

// New table to normalise the inspection types
inspection_types (
    inspection_type_id INT UNSIGNED NOT NULL AI,
    type_name VARCHAR NOT NULL
    .... other rows ....
)

// Normalised table to replace each {fruit} table
inspection_data (
    inspection_data_id INT UNSIGNED NOT NULL AI,
    inspection_id INT UNSIGNED NOT NULL (links to inspections.inspection_id)
    .... other rows ....
)

Then your query would be simply

SELECT * 
FROM inspections

INNER JOIN inspection_types
ON inspection_types.inspection_type_id = inspections.inspection_type_id

INNER JOIN inspection_data
ON inspection_data.inspection_id = inspections.inspection_id

The brief overview above is quite vague because your existing table data hasn't really been specified, but the general principle is sound. It wouldn't even take much to migrate data out of your existing structure, but when it's done it'll give you far cleaner queries and allow you to actually get the data you're after out more easily

Answer 2

Since it is not possible to have a variable for a table name directly in TSQL, you will have to dynamically construct the TSQL.

Variable table names in Stored Procedures