MySQL Query - use every table except this, this and this!?

Question

Hi ppl, im trying to get a query to look in all tables that begin with "com" or exclude the 4 tables that are not needed in the query. The query will then go through every table looling to see if the student is part of that class.

I know i could use something like -

SELECT * FROM com3453, com345 etc... where Studentnumber = $snumber;

But this would take forever as there are many com's or different classes.

So is there any way to run a query on tables of string "com" at the start or exclude a few tables???

Thanks

Answer 1

show tables like 'com%';

Exceptions would be trickier, you could insert the results of show tables into a temporary table and then select all results from that where name NOT IN (list) or you would use string or array manipulation in whatever programming language you're using.

BTW, your query will not work, you're doing a cross join, but from your description what you want is a union (and a subselect).

select * from
(select * from com3453
union
select * from com345
union
select * from etc) as combined
where Studentnumber = $snumber;

Answer 2

What you're attempting to do is easy, yet you're going approximately it the incorrect way. you could create the sq. question using the common techniques like SUM and count type and definite you need to use group by using in any different case get right of entry to won't understand the question

Answer 3

hm.. i think what you need is 'Dynamic SQL', passing table or field name as parameter.
if i'm not mistaken, only for mySql 5 can do that (i don't know about mySql ver. 4),

Here some sample about Dynamic Sql in Stored Procedure :
http://forge.mysql.com/snippets/view.php?id=13