I have a join problem in a one-to-many relationship.
Table 1
id, varr
1, val1
2, val2
3, val3
4, val4
5, val5
.
Table 2
id, t1id, varr
1, 1, somevalue
2, 1, somevalue
3, 2, somevalue
4, 4, somevalue
5, 1, somevalue
6, 5, somevalue
.
Now, I need to select all rows from Table 1, along with the COUNT of rows in Table 2, where t1id equals Table1->id
However, I don't know how to get rows from Table 1 if there are no entries in Table 2, so that the count(t2.id) with the given WHERE clause would be 0.
I understand the flawed logic in this query (WHERE matches only records where the eval is true), but I don't know how to "join with unexistant" t2.t1id (in the lack of a better term)
SELECT t1.varr, count(t2.id) FROM table_1 AS t1, table_2 AS t2 WHERE t1.id=t2.t1id GROUP BY t1.varr
In the above example, this query would not output row 3 (with id=3) from Table 1, as there are no rows in Table 2 with t1id=3.
I know how to solve the problem using two queries in PHP (by getting the counts first and then by ouputting 0, or counts for each row in second query for Table 1). But I was wondering if this at all is possible with SQL in one single query.
