My SQL table select...

drucifer

Ok, i've searched the archive and can't find the answer to what is hopefully a very simple question...

Can anyone tell my how to 'SELECT whatever FROm anytable'...

I want to be able to querie all tables in a database without having to manually list them. Have tried '*' but that dies...is there a simple answer.

(sorry, I know this is really mysql and not php but their forum never helps)

Anon

SELECT * FROM TABLENAME
it'll work fine check it?

drucifer

from problem is, I need TABLENAME to apply to all the tables in the database...

the intention is that users will be able to add new tables when needed and so in the search form i want them to be able to either choose one particular table (already coded) or search across all tables....

[deleted]

You have to specify which table you want to search in.
That's the way SQL works.

And you can't simply say

SELECT * FROM table1, table2, table3 WHERE username='john';

because that will give you a cartesian product, which can he huge.

drucifer

The table choice is part of a form which involves many other search criteria as drop down menus so the result size should not be a problem. The problem is that i know how to search numerous tables by listing their names but not a way of just saying 'all of them' ... is it really not possible...?

[deleted]

The resultsize will be a problem if you do a query like

SELECT * FROM table1, table2 WHERE username = 'bert';

This will give a cartesian product, and that will be huge. (nxn rows)
If your tables are 100 and 200 rows, you already get 20k rows in the result.

If you are looking for seperate rows in seperate tables, you'll need to query each table seperately.

drucifer

so it's not possible to search all tables on a database?

drucifer

even if there are multiple 'where' clauses in the querie....

[deleted]

Ofcourse you can search all tables in a database, but there's no method of searching all of them like:

SELECT FROM WHERE this=that.

Like I said, if you can't JOIN the tables on some common column-data, you cannot select more than one table per query.

If you do
SELECT * FROM table1, table2 WHERE table1.col1 = 'this' OR table2.col1 = 'this';
you will get a cartesian product (did you look up taht term yet?) which means that for every match in the first table, you will get EVERY row from the second, and vice versa.
And that is definately NOT what you want.