CODEWITHSUNDEEP
mysqlsqlinner-join
Csharp
Csharp

Reputation: 177

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

Table 1 :

ID  City             State
1   NewYork          NY
2   Oklahama         OK
3   california       CA
4   new jersey       NJ
5   Las Vegas        LA

Table 2 :

ID  City 
1    NewYork          
2    NewYork          
3    NewYork          
4    Oklahama
5    Oklahama

NewYork is 3 times and Oklahama is 2 times in table 2. So I want to fetch City List from Table 1 which Cities are used less then 5 times in Table 2

So what will exact query in Mysql?

I am using below code :

select *
from  Table1
where Table1.city in
    (select Table2 .city,count(*)
     from Table2
     having count(*) < 5
     group by Table2.city )

Upvotes: 0

Views: 90

Answers (2)

Tony
Tony

Reputation: 10327

How about

select Table1.city
from Table1 inner join Table2 on Table1.city = Table2.city
group by Table1.city
having count(Table2.city) < 5

SQLFiddle

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

You can use a in clause with a subselect 

    select  * from  table1
    where city in (select  city from table2 having count(*) < 5 group by city)

In your code you have a space between the Table2 and .city 

select  * 
from  Table1  where Table1.city in   (select  
              Table2 .city,count(*) from Table2 having count(*) < 5 group by Table2.city )

must be 

select  * 
from  Table1  
where Table1.city in   (select  Table2.city
                        from Table2 
                         group by Table2.city
                         having count(*) < 5   )

no space between tablename and column ..

you can seee http://sqlfiddle.com/#!9/556cb6/10

Upvotes: 1

Related Questions