Mysql join gives duplicate rows

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:

pos_product_selling table:

Output:

EDIT

Whe开发者_运维百科n 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 ?

---

Try something like these

GROUP BY pos_product_selling.metrics

---

try this:

SELECT DISTINCT * FROM ...
GROUP BY pm.metrics

---

Add a primary key in the pos_metrics table and introduce it to the pos_product_selling table, then do a JOIN based on the primary key as well as the other criteria. You won't get these duplicates then.

The reason you have duplicates over here is because there is no possibility of an unique comparison to be done on both tables based on a value.

---

To eliminate dups, use distinct:

select distinct * from ...

But I've a feeling your question is about something else -- and that you'd need to post the specific code for more help.

---

SELECT * FROM pos_metrics pm, pos_product_selling pps
Where pm.p_id=pps.p_id AND pm.p_id='0' AND pps.pos_buying_id='0' AND pm.type=1

try the above query

---

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.