SQL JOIN duplicating rows

Question

I am trying to join 2 tables and grab a single row for each item. The default value for the rating is 45. Every time there is a different rating within a category, each item row is duplicated. Below is an example schema, data, and result.

Table 1
--------------------------
ofid | item | category
--------------------------
1 | item1 | cat1
2 | item2 | cat1
3 | item3 | cat1

Table 2
---------------------------
ofid | rating | category
---------------------------
1 | 37 | cat1
2 | 42 | cat1
3 | 45 | cat1

Result
------------------------------------
ofid | item | rating | category
------------------------------------
1 | item1 | 37 | cat1
1 | item1 | 42 | cat1
1 | item1 | 45 | cat1
2 | item2 | 37 | cat1
2 | item2 | 42 | cat1
2 | item2 | 45 | cat1
3 | item3 | 37 | cat1
3 | item3 | 42 | cat1
3 | item3 | 45 | cat1

Expected Result
------------------------------------
ofid | item | rating
------------------------------------
1 | item1 | 37
2 | item2 | 42
3 | item3 | 45

Query:
SELECT p.ofid,p.item,r.rating FROM table1 AS p INNER JOIN table2 AS r ON p.category = r.category WHERE p.category = "cat1" GROUP BY p.ofid,p.category,r.rating

I've tried adding DISTINCT, JOIN, LEFT JOIN, RIGHT JOIN.... help?

Thanks in advance.

Answer 1

According to what you want in your expected result, I believe you are joining on the wrong field. Below should work.

Select
  p.ofid,
  p.item,
  r.rating
From table1 p 
Inner Join table2 r On r.ofid = p.ofid
where p.category ="cat1"

Answer 2

Maybe I'm misunderstanding but don't you want to join on the ofids as well?

SELECT p.ofid,p.item,r.rating 
FROM table1 AS p 
INNER JOIN table2 AS r 
    ON p.category = r.category AND p.ofid = r.ofid 
WHERE p.category = "cat1" 
GROUP BY p.ofid,p.category,r.rating

Answer 3

That is a perfectly normal result, as you're grouping on all 3 fields you're selecting. If you want a SINGLE copy of each ofid in the result set, then do a GROUP BY p.ofid only.

Answer 4

You probably want to join on ofid in addition to (or instead of?) category. All three entries in table1 have "cat1", and all three entires in table2 also have "cat1", so each record in table1 matches all three records in table2. Thus, 3 x 3 = 9 rows expected in the result. You probably want something like this:

SELECT p.ofid,p.item,r.rating
FROM table1 AS p
INNER JOIN table2 AS r ON p.category = r.category
                       AND p.ofid = r.ofid
WHERE p.category = "cat1"
GROUP BY p.ofid,p.category,r.rating

Answer 5

I believe this is what you are trying to do?

SELECT p.offid,p.item,r.rating,r.category FROM table1 p INNER JOIN table2 r
ON p.category = r.category
AND p.offid = r.offid
WHERE p.category = 'cat1'