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

Question

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 )

I was looking for Query So By Mistake i wrote Jquery. Please vote my Question. — Csharp
– Csharp, Commented Jun 26, 2016 at 18:34

ScaisEdge · Accepted Answer · 2016-06-26 19:59:23Z

1

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

edited Jun 26, 2016 at 19:59

answered Jun 26, 2016 at 18:33

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Csharp Over a year ago

Its not worked for me.I am comparing city from table 1 to Table 2.I want city list from table 1 which is used in table 2 less then 5 times.

ScaisEdge Over a year ago

I have update the answer ..looking the sample provided you question was not so clear .. l

Csharp Over a year ago

I checked this it is showing me error on group by clause.Just to be clear again I want to fetch City from table one which city is another table less then 5 times.Lets say NewYork is in table one.and in table two there are four row which contains NewYork 3 times.So i want data from table one which is less then 5 in table 2.Thanks.

ScaisEdge Over a year ago

You can show me the error message please ...i have update the answer removing distinct (not needed)

Csharp Over a year ago

I am getting this error: Error Code: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group by city'

|

Tony · Accepted Answer · 2016-06-26 20:14:06Z

1

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

edited Jun 26, 2016 at 20:14

answered Jun 26, 2016 at 19:59

Tony

10.4k3 gold badges51 silver badges78 bronze badges

1 Comment

Csharp Over a year ago

Thank you so much @Tony.This worked for me.This is actually what i want.

Collectives™ on Stack Overflow

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

2 Answers 2

9 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related