Mysql join gives duplicate rows

user_402610 0
Viewed 45294

I have 2 tables and i am using join to get common records from those 2 tables. i have used the following query but my problem is i am getting the records doubled. The query is as follows

SELECT * FROM pos_metrics pm INNER JOIN pos_product_selling pps ON   
pm.p_id=pps.p_id WHERE pm.p_id='0' AND pps.pos_buying_id='0' AND pm.type=1

pos_metrics table:
enter image description here

pos_product_selling table: enter image description here

Output:

enter image description here

EDIT
When I tried to use GROUP BY and DISTINCT together I am not getting duplicates but the value from the second table is repeated. Any other solutions ?

left-join join mysql
6 Answers

After reading this subject I could not think there was something going on. I had the same issue and nailed it down to one table which was

CREATE TABLE `$dbname`.`$table` (
`prtcatid` INT NOT NULL,
`prtcat` VARCHAR(45) NULL,
);

The only difference between this table and all the others was there was no primary index. the code was changed to

CREATE TABLE `$dbname`.`$table` (
`prtcatid` INT NOT NULL,
`prtcat` VARCHAR(45) NULL,
PRIMARY KEY (`prtcatid`)
);

the doubling of the rows then ceased. This solution was prompted by the suggestion in an previous answer above. Hope this may help others.

user_41259830
Related