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 <listings_[ID]> 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=arch...@jab.org
