mysql count from table where related record exist in other table

Question

I have 2 tables, customers and addresses. I want to count how many customers have addresses with name like a given search term

something like

SELECT COUNT(*) as countSearch FROM customers,addresses WHERE address.cu_id=customer.id AND customer.name like ?

Table Customers

 ________________
| id   |  name   |
 _________________
|  4   |  john   |
|  5   |  mark   |
 _________________

Table address

| id   |  cu_id   | address  |
 ____________________________
|  1   |  4       | ADRESS!! |
 _________________

Use explicit rather than comma joins. AND What;s the question do you not understand how like works or something else? — P.Salmon, Aug 14 '20 at 12:06
have a look at this https://stackoverflow.com/questions/1521605/sql-server-query-selecting-count-with-distinct — Ebrahim, Aug 14 '20 at 12:53

score 1 · Accepted Answer · answered Aug 14 '20 at 12:08

1

You can use a inner join to connect both tables

This wouldm show how many addresses c.name has

SELECT COUNT(*) as countSearch 
FROM customers c INNER JOIN addresses a
     ON a.cu_id  = c.id
WHERE c.name like 'test'

answered Aug 14 '20 at 12:08

nbk

1 Answers1