INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Help with Query

Help with Query

(OP)
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


CODE

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

Table : custom(listid) eg

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 clown )' 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
 

RE: Help with Query

Quote:

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close