Marco, all,
Marco Bartz wrote:
> I accidentally sent it before finishing...
>
> I am looking for a way to do the following with a single query:
>> SELECT `ID`, `Name`, `Interface`,
>> (SELECT count(*) FROM CONCAT('listings_', `ID`) WHERE `Status`='Active') as
>> `activeListings`
>> FROM `sites`
There is no such thing as "dynamic identifiers" in SQL:
You cannot construct any identifier (like database, table, or column
name) inside an SQL statement.
>
> I am querying the sites table and I want to have a field showing active
> listings.
>
> Each row in the sites table has a corresponding 'listings' table. so a site
> where `ID`=1 will have a listings_1 table.
If you keep your current database schema (not change it, as proposed by
Nigel Wood), your only chance is "dynamic SQL":
construct the whole SQL statement at run time, within your application.
The MySQL command line client supports this in "prepared statements",
and of course any programming language (like Perl, PHP, ...) that
provides string operations (and allows using strings for SQL statements)
also supports this.
>
> If I want to return data from 100 sites is there a way to get the active
> listings count without making 100 extra querys?
If the data of these 100 sites is stored in 100 different tables, you
need SQL statements accessing them.
If you like, you can try with a single big UNION of 100 SELECTs, but I
would not advise you to do so.
HTH,
Jörg
--
Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com
Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel
Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
