CODEWITHSUNDEEP
mysqljoinleft-join
Deepak
Deepak

Reputation: 6802

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:
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 ?

Upvotes: 22

Views: 52035

Answers (6)

Tony
Tony

Reputation: 1

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.

Upvotes: 0

Jayakrishnan K
Jayakrishnan K

Reputation: 267

Try something like these 

GROUP BY pos_product_selling.metrics

Upvotes: 22

Shyam Natraj Kanagasabapathy
Shyam Natraj Kanagasabapathy

Reputation: 1901

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.

Upvotes: 8

nunu
nunu

Reputation: 2861

try this:

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

Upvotes: 11

gmhk
gmhk

Reputation: 15940

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

Upvotes: 1

Denis de Bernardy
Denis de Bernardy

Reputation: 78413

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.

Upvotes: 5

Related Questions