Problem with Dynamic table names.

--0016369203f32bd9eb046d77f29c
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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`

--0016369203f32bd9eb046d77f29c--

Re: Problem with Dynamic table names.

--0016e6409bce2ac206046d781077
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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`
>

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 I want to return data from 100 sites is there a way to get the active
listings count without making 100 extra querys?

--0016e6409bce2ac206046d781077--

Re: Problem with Dynamic table names.

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`
>>
>>
>
> 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 I want to return data from 100 sites is there a way to get the active
> listings count without making 100 extra querys?
>
>
At first glance your table design looks to be sub optimal, a table per
site certainly isn't normalised particularly if the structure of each
listings table is the same. Do you have this structure for performance
or archiving reasons?

You certainly could do this in two queries if you used the results of
the first to build a large write a left join or UNION query for the
second but it maybe just be using clever SQL to make up for poor table
design and not scalable in the long term. The queries would be:

select ID from sites;

SELECT `ID`, `Name`, `Interface`, count(*) as active
FROM `sites`
**** begin one left join per ID ****
left join on where site.id = '[ID]' and listings_[ID].status = 'Active'
**** end one left join per ID ****
group by sites.id;


Assuming you've a good reason for the table design perhaps a merge table
is more what you need?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Problem with Dynamic table names.

Marco, all,


Marco Bartz wrote:
> I accidentally sent it before finishing...
>=20
> 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`=
=3D'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.

>=20
> I am querying the sites table and I want to have a field showing ac=
tive
> listings.
>=20
> Each row in the sites table has a corresponding 'listings' table. s=
o a site
> where `ID`=3D1 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 applicatio=
n.
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 statemen=
ts)
also supports this.

>=20
> If I want to return data from 100 sites is there a way to get the a=
ctive
> 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

--=20
Joerg Bruehe, MySQL Build Team, Joerg.Bruehe@Sun.COM
Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel
Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB1610=
28


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg