CODEWITHSUNDEEP
mysqlsql
Irma Elita
Irma Elita

Reputation: 249

How to count by joining two tables

I have 2 tables like this.

Table : Family Members

----------------------------------
|Address                 | Name  |
----------------------------------
|North Jakarta City      | Andra |
|North Jakarta City      | Halim |
|South Jakarta City      | Irma  |
|Thousand Island Village | Dian  |
----------------------------------

Table : Member Details

---------------
| Name  | Age |
---------------
| Andra | 1   |
| Halim | 50  |
| Irma  | 20  |
| Dian  | 4   |
---------------

What is the correct query if I want to count members between the ages 0 and 4 who live in a 'city'? I've tried using this query but the result is incorrect. The correct result should be 1 since only Andra who lives in a city and between the ages 0 and 4. Please help me.

SELECT COUNT(family_members.name) AS total FROM family_members, member_details 
WHERE family_members.address LIKE '%City%' AND member_details.age BETWEEN 0 AND 4

Upvotes: 1

Views: 57

Answers (2)

th3sly
th3sly

Reputation: 1881

You should consider redesigning your database like:

----------------------------------
| user_id | Name  | Age | city_id |
----------------------------------
|    1    | Andra | 1   |    1    |
|    2    | Halim | 50  |    1    |
|    3    | Irma  | 20  |    1    |
|    4    | Dian  | 4   |    2    |
----------------------------------


------------------------------------
|city_name               | city_id  |
------------------------------------
|North Jakarta City      |    1     |
|Thousand Island Village |    2     |
------------------------------------


SELECT COUNT(*) AS total 
FROM family_member
JOIN city on family_member.city_id = city.city_id
WHERE city.city_name= 'City' AND family_member.age BETWEEN 0 AND 4;

Also you should add indexes.

Upvotes: 0

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

You need a join

SELECT COUNT(fm.name) AS total 
FROM family_members fm
Join member_details md on md.Name = fm.Name
WHERE fm.address LIKE '%City%' AND md.age BETWEEN 0 AND 4

with you syntax, you may add this in the where clause (because your query will generate a cartesian product).

BUT : you should really use the JOIN syntax

AND family_members.Name = member_details.Name

EDIT By the way, I would strongly suggest to use surrogate keys in your tables (a name is not really something unique)

Upvotes: 4

Related Questions