[SOLVED] Combining two queries. JOIN, UNION and subqueries haven't worked for me.

[Petsmacker]

I have two tables.

 

table1 has a field called user_id

table2 has a field also called user_id and another one called ready

What I'm trying to do is count how many rows there are in table1 where the user_id's in table1 and table2 are the same AND where ready in table2 in those selected rows is equal to 1.

 

This is one of the SQL queries I've tried so far but to no avail.

 

SELECT count(*) FROM table1 INNER JOIN table2 ON table1.user_id=table2.user_id AND table2.ready=1

 

This one vaguely works but with the problem that all queries are multiplied by the number of rows in table1. Any way I can get this right?

[fenway]

I don't see why you need to use table1 at all.

[Petsmacker]

Cos only the rows from table1 where ready is equal to 1 in table2 are needed, the information is in two different tables. Just doing a count() of table2 doesn't do the job.

[fenway]

I was confused by the query above... so there's a many-to-many relationship here?

[Petsmacker]

Not sure what many-to-many means exactly so I'll just explain the general setup. I'm surprised how such a simple request seems to be so difficult to answer, not just on this forum.

 

Part of my site is about 'rating' other peoples content that they've created. So table1 is the 'Ratings' table. Because lots of people will rate the same thing, lots of rows are created for each person that posts a rating.

 

The thing they're actually rating is in table2. Each user can have multiple things to be rated, all contained in multiple rows in table2. If the 'ready' field is 1 on any of these things, the number of ratings for it should be 'count()'ed. The things in table2 where the 'ready' is 0 should be ignored.

 

Thats basically it, any ideas?

[fenway]

Not sure what many-to-many means exactly so I'll just explain the general setup. I'm surprised how such a simple request seems to be so difficult to answer, not just on this forum.

Many-to-many would mean that the same row in table1 can be related to multiple rows in table2, and vice versa.

 

And the reason it's so "difficult" is because you've presented vague generalities without giving the real-world example.  And you've left out fields too --- like the rating aspect -- so its hard to know what's in the tables.  You may think it's not relevant, but that doesn't make it any eaiser for the rest of us to figure out what you already know.

 

Based on what you've just recently posted, I understand the tables as follows:

 

"table1" = rating - ( item_id, user_id, rating_value )

 

"table2" = item - ( item_id, user_id, ready )

 

I assume the user_id in the item table refers to the person who created the item, whereas the user_id in the rating table refers to the person actually rating the item.  So this is a one-to-many relationship -- each item can have multiple ratings, but not vice-versa.

 

If this is all true, then you need to be joining on item_id, NOT user_id.

 

select count(r.item_id) from ratings as r inner join items as i using ( item_id ) where i.ready = 1

 

There should be no "multiplying" as you indicated above, since there is only one item record for each ratings record.