Multiple Query w/ Count

I'm trying to query 2 tables,
List the details from one and count total rows from another where Id's are the same.

$dbQuery = "SELECT reviewId FROM dataReviews";
$dbQuery = "SELECT criticDataId FROM criticRating";

$totalCount = mysql_query("SELECT COUNT(*) AS count FROM criticRating WHERE criticDataId = '$reviewId' ");

Example:
-------------------------------------------
dataReviews table
reviewId <= primary key
1 | 2 | 3

criticRating table
criticDataId <= not primary key
2 | 3 | 2 |1 | 2

reviewId 1 has a total of 1
reviewId 2 has a total of 3
reviewId 3 has a total of 1
-------------------------------------------

I also tried something like this.

$queryDetails = "SELECT dataReviews.reviewId, criticRating.criticDataId FROM dataReviews, criticRating";

$totalCount = mysql_query("SELECT COUNT(*) AS count FROM criticRating.criticDataId WHERE criticReviewId = '$reviewId' ");

I hope someone can understand what I'm trying to do here.

1. Are you trying to count the number of times each value in criticRating.criticDataId appears in the table

2. Would it be already known that the values in criticRating.criticDataId are the same values in dataReviews.reviewId or would they not be the same? (i.e. is criticRating.criticDataId the foreign key for dataReviews.reviewId?)

Yes laidbak,
I'm trying to count the number of times a dataReviews.reviewId appears in the table criticRating.criticDataId. They would be the same value.

I want to query dataReviews and then query criticRating and count how many times reviewId is in criticDataId.

So if I have a dataReviews.reviewId of "1"
and in criticRating.criticDataId there are 3 rows with the value of "1", then it would be "reviewId has 3 criticDataId counts".


dataReviews.reviewId is auto_increment and criticRating.criticDataId is just a updated int(11).

hope that helps. thnanks

select criticDataId, count(criticDataId) As 'count' from criticRating GROUP BY criticDataId;

O.k that got me going in the right direction. Here's what I did to get the results back I needed.

$resultID = mysql_query("SELECT dataReviews.reviewId, criticRating.criticDataId, count(criticRating.criticDataId) As 'count' from criticRating, dataReviews WHERE dataReviews.reviewId = criticRating.criticDataId GROUP BY dataReviews.reviewId ");

Thanks Again! :-)

Ok, great.
I guess my only question would be why you need to include the table "dataReviews" in this query.

It seems all the data you need would be in the "criticRating" table.

The reason I have to include dataReviews.reviewId is to get the ID of the Review,

I have all information of a Item in dataReviews, And a few rating values in criticRating. I wanted to keep the data and rating separated, one reason is I also had a TIMESTAMP in dataReviews, which kelp going back to NOW() every time I rated.

I just moved all the rating to another table.

Over All looks something like.
Review ID | Title | Total Votes
1 | t1 | 3
2 | t2 | 4
3 | t3 | 6

This is my first time doing something like this, so there may be a easier way.

Yes, there are better ways to do it, however, one rule of thumb for programmers is "If it isn't broke don't fix it".

I do agree with you there...

Thanks again for all your help.

Anytime... take care.