I have two tables that I'm working with One dependent on the other. And I would like to get the information of the independent tables and count the number of times each row of data was used by the dependent table. With My current query I only get a record count of 1 when I know there in fact 38 records total. What am I doing wrong?
SELECT r.rid, r.name, COUNT(b.bid) AS brewtot
FROM recipes r, brews b
WHERE r.uid = '#cookie.id#' AND b.rid = r.rid
I suspect what you want to do is add a
GROUP BY b.rid
assuming your question is "How many brews are there for each recipe."
Also you might want to use a LEFT JOIN to also count 0 rows when there is no brew to a recipe:
SELECT r.rid, r.name, COUNT(b.bid) AS brewtot
FROM recipes r LEFT JOIN brews b
ON b.rid = r.rid
WHERE r.uid = '#cookie.id#'
GROUP BY b.rid
select r.rid, r.name, count(b.bid) as brewtot
from recipes r inner join brews b on r.rid = b.rid
where r.uid = '#cookie.id#' group by r.rid, r.name
That should work
Add 'GROUP BY r.rid' at the end of your query.
You have to do group by on rid.
SELECT r.rid, r.name, COUNT(b.bid) AS brewtot
FROM recipes r, brews b
WHERE r.uid = '#cookie.id#' AND b.rid = r.rid
group by r.rid
Do you want to also see all brews record too?.
I'm surprised the query even works without a group by? Try:
SELECT r.rid
, r.name
, COUNT(b.bid) AS brewtot
FROM recipes r
JOIN brews b
ON b.rid = r.rid
WHERE r.uid = '#cookie.id#'
GROUP BY
r.rid
, r.name
On SQL Server with my test setup, this returns the correct count.
