mysql - Count rows from another SQL table in the same query

Question

I have 2 tables from which with only one query I have to get the result I have the url table and the vote table.

Table url

|id           |url            |category|
|.............|...............|........|
|1            |example1.com   |1       |
|2            |example2.com   |1       |
|3            |example3.com   |2       |
|4            |example4.com   |1       |

Table vote

|id           |note        |
|.............|............|
|1            |1           |
|1            |2           |
|1            |1           |
|2            |1           |

I wanted to do a query that shows me all the fields of the url table and the count of all the rows with note 1.

I have tried with this query:

SELECT u.id, u.url, u.cat, COUNT(*) as countnote 
FROM vote v, url u 
WHERE u.cat=1 and v.note=1

but it only shows me one row.

Expected result:

|id           |url            |countnote|Category|
|.............|...............|.........|........|
|1            |example1.com   |2        |1       |
|2            |example2.com   |1        |1       |
|4            |example4.com   |0        |1       |

Thank you for your help

question from:https://stackoverflow.com/questions/65918051/count-rows-from-another-sql-table-in-the-same-query

Answer 1

I would just suggest a correlated subquery:

SELECT u.*,
       (SELECT COUNT(*)
        FROM vote v
        WHERE v.id = u.id AND v.note = 1
       ) as num_note_1
FROM url
WHERE u.cat = 1;