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?

I think you have stumbled upon the reason why this DB architecture is a bad idea. — JohnFx, Feb 21 '12 at 00:00

score 1 · Accepted Answer · answered Feb 20 '12 at 23:58

1

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

answered Feb 20 '12 at 23:58

Mitch Wheat

295,962
43
465
541

http://stackoverflow.com/questions/1325044/dynamic-sql-passing-table-name-as-parameter – Mitch Wheat Feb 20 '12 at 23:59
http://stackoverflow.com/questions/3646412/mysql-query-using-variable-as-table-name-in-left-join – Mitch Wheat Feb 21 '12 at 00:01
http://stackoverflow.com/questions/2754423/use-a-variable-for-table-name-in-mysql-sproc – Mitch Wheat Feb 21 '12 at 00:01
I agree this is a horrible idea. I"ll figure out a way to pass the name through as a variable in JQuery instead. – ehime Feb 21 '12 at 00:05
A better idea would be to combine the inspection type tables into a single table so you don't need to deal with all this foolishness. – JohnFx Feb 21 '12 at 00:10
I agree but they have vastly different column sizes. This is really my first stab at creating a DB and I'm still trying to learn the do's and don't's this seems like a huge _don't_ it you ask me. – ehime Feb 21 '12 at 00:18
This answer is a NASTY way to go about it, this is a bigger don't than working on normalising your tables but hey ho as you can use a normalisation table to match inspection_type to an integer id. – Simon at The Access Group Feb 21 '12 at 00:30
I didn't comment on the validity of the solution, as whenever I've done so in the past I get critised for answering with the real solution rather than simply answering the question! So Yes, haiving the tablenames in a table like that is a horrible solution. – Mitch Wheat Feb 21 '12 at 03:54
It is, unfortunately it was how its set up, I can only be so flexible =( I managed to pass these with jquery so it was `123:melons` is posted, as `123:melons => on` then values are exploded and dropped, then foreached through with the DB fetch_array class I wrote. Still seems stupid, but safer I think. – ehime Feb 21 '12 at 16:26

score 1 · Answer 2 · answered Feb 21 '12 at 00:09

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

Mysql sub queries?

2 Answers2