lundi 18 juillet 2016

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 )

Aucun commentaire:

Enregistrer un commentaire