Help with Query

[mt1982]

Hi,

Im new to the world of mysql and I'm after help with a query I'm trying to simplify.Here is a simplified idea of what part of my database looks like

Table: table1
==============================
| leadid | listid | username |
==============================
|   1    |   aa   | user001  |
|   2    |   bb   | user002  |
|   3    |   cc   | user003  |
|   4    |   aa   | user004  |
|   5    |   aa   | user005  |
|   6    |   bb   | user006  |
==============================

[b]Table : custom(listid) eg[/b]

Table : customaa                 
=======================         
| leadid | other data |         
=======================         
|   1    |    2001    |         
|   4    |    2002    |         
|   5    |    2004    |         
=======================

Table : custombb
=======================
| leadid | other data |
=======================
|   2    |    2002    |
|   6    |    2011    |
=======================

I need to be able to perform queries based on username and 'other data'


I have data stored in multiple 'custom' tables, I will be adding more of these in future. Each leadid only appears once in any of the 'custom' tables. Is there any way to do a 'select * from (multiple variable table names that maybe added to in future ) )' or 'select * from custom(wildcard)'

At the moment I'm using PHP to select distinct listid from table1 - storing that in an array and then performing a query to search all the custom tables but these queries take a long time to run.


Hopefully I've explained what I'm trying to achieve if you need any more information please let me know

 

[r937]

Is there any way to do a 'select * from (multiple variable table names that maybe added to in future clown )' or 'select * from custom(wildcard)'

not easily

best you can do is code up one SELECT for each custom table, and then UNION ALL all the results together

SELECT * FROM customa
UNION ALL
SELECT * FROM customb
UNION ALL
SELECT * FROM customc
...

the better solution, based on what i see in customa and customb, is to combine all these tables into one

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon