Get data from one table by reference of another table's column's count(*)

debugcn Published at Dev

Csharp

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;

O. Jones

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.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-06-26

Comments

0 comments

From Dev

set a value to column in one table with another table's column data

From Dev

Fetch data from Table 1 column's count in Table 2

From Dev

select data from one column based on another column's condition in the same table and insert that resulting data to another table

From Dev

Related Related

Article