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;

Answers


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.


Need Your Help

How to easily rotate 180degrees a GridLayout in Java?

java swing awt layout-manager grid-layout

within a java chess application, I need at different points in time to "invert" the GridLayout representing the board. (after the turn of White/Black for example, or simply at the user's request to...

Getting first image in gif using carrierwave

ruby-on-rails ruby carrierwave rmagick

Im using carrier wave to upload gifs which works just fine, the problem comes when i try to generate the thumb version and converting the gif into a jpeg with only the first image in the gif as the...