Pulling * from multiple, identically structured tables

[schwim]

Hi there folks!

Each of these identically structured tables holds the model of the vehicle and the associated price. I need to create a single array(my result) of all these model/prices to use.

MySQL.Async.fetchAll('SELECT * FROM vehicles', {}, function(result)

Works while:

MySQL.Async.fetchAll('SELECT * FROM commercial, motorcycles, muscleandclassics, supercars, truckandsuvs, vehicles', {}, function(result)

does not. I’m unable to retrieve an error from the second failed query but I’ve stopped retrieving the result so I’m wondering what I need to do to get my result to retrieve the content from those tables to add to the result.

[r937]

your second query –

@schwim

SELECT * FROM commercial, motorcycles, muscleandclassics, supercars, truckandsuvs, vehicles

is a humoungous cross join (also known as cartesian product)

what you’re looking for is a union query

SELECT * FROM commercial
UNION ALL
SELECT * FROM motorcycles
UNION ALL
SELECT * FROM muscleandclassics
UNION ALL
SELECT * FROM supercars
UNION ALL
SELECT * FROM truckandsuvs
UNION ALL
SELECT * FROM vehicles

using the dreaded, evil “select star” will work only if all tables do indeed have exactly the same structure

i will leave it to someone else to let you know that these shouldn’t be separate tables at all

[DaveMaxwell]

@r937

i will leave it to someone else to let you know that these shouldn’t be separate tables at all

[m_hutley]

@r937

i will leave it to someone else to let you know that these shouldn’t be separate tables at all

was 100% the first thought at the words ‘identically structured tables’

If the tables are identically structured, make them all one table with an additional column that identifies their type. It’ll save your head a lot of hassle trying to write queries.