Mysql join gives duplicate rows

Question

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

Solution 1

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.

Solution 2

Try something like these

GROUP BY pos_product_selling.metrics

Solution 3

try this:

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

Solution 4

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.

Solution 5

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

View more solutions

Comments

[Denis de Bernardy]

My feeling is you need a group by/having somewhere, but your question is lacking context.

[Denis de Bernardy]

Well, yeah, that's the expected result. Shouldn't you be using something like: select distinct pm.* from ...?

[Deepak]

this is working but is this the right way to do ?? i am dealing with thousands of record here..

[Ricky Boyce]

What do you mean by "and introduce it to the pos_product_selling table"

[Martin Prikryl]

Can you comment your answer?

[Favourite Onwuemene]

@Ricky B He means create a column on the pos_product_selling table whose value is a primary key on pos_metrics. A typical name for such a column would be "pos_metrics_id"

[ERIC]

INNER JOIN may create unique rows with only one different column, so distinct may not reduce result.

[kiamoz]

Answer was easy but not shown lol , thank you Jayak ;)