CODEWITHSUNDEEP
mysql
zero742
zero742

Reputation: 11

SQL JOIN duplicating rows

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.

Upvotes: 0

Views: 120

Answers (5)

questborn
questborn

Reputation: 2855

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'

Upvotes: 0

Wiseguy
Wiseguy

Reputation: 20873

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

Upvotes: 0

Spencer Ruport
Spencer Ruport

Reputation: 35107

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

Upvotes: 1

John Hartsock
John Hartsock

Reputation: 86872

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"

Upvotes: 2

Marc B
Marc B

Reputation: 360572

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.

Upvotes: 0

Related Questions