Get data from one table by reference of another table's column's count(*)
I have two tables "domaininfo"" and "Events".domain info has the column name "city" in the same way table name "events" has the column name "city".In this case I want fetch the data from domainname where in events there are the record >200.I want the record which are more then 200 in from domainname reference to city in both table. menace The reference city names in "evets" table morethan 200 which are already in domainname.I tried this queries.
Table Structure of Domaininfo ID City state Country
Table Structure of Events EventName Address City Description
City column has Cities in "domaininfo" table from which in can the reference to fetch the events.So i want the record from "domianinfo" of those cities which have the events in the "events" table more than 200.
For Example: domain info has city name "New York" in that case i want to check whether there are the events more than 200 in the "events" table.If "events" table will have the records more than less than 200 it will give me the record from "domaininfo" table. select count(*) from wpcommon.domaininfo inner join events by city HAVING COUNT(evets)>200; select count(*) from wpcommon.domaininfo where wpcommon.evets having count(*)>200; select count(*) from wpcommon.domaininfo where wpcommon.evets.select count(*)>200;
Let's build this up, taking advantage of the S in SQL (structured).
First, for what values of city are there more than 200 events?
SELECT COUNT(*) AS eventcount, city FROM events GROUP BY city HAVING COUNT(*) > 200
That subquery produces the list of cities you want.
Next, let's join that to your list of events.
SELECT d.something, d.somethingelse, d.whatever, d.whatelse FROM domaininfo AS d JOIN ( SELECT COUNT(*) AS eventcount, city FROM events GROUP BY city HAVING COUNT(*) > 200 ) AS big ON d.city = big.city
That structured (nested) query will yield the result you hope for.
But beware, the city name by itself isn't guaranteed unique in the USA. Consider "Kansas City" which exists in both the state of Kansas and Missouri. You may want to work with the city/state combination, or with the id.