Menu
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 ?
590
In some cases, you need to join tables by multiple columns. In these situations, if you use only one pair of columns, it results in duplicate rows.
Solution. Select column values in a specific order within rows to make rows with duplicate sets of values identical. Then you can use SELECT DISTINCT to remove duplicates. Alternatively, retrieve rows in such a way that near-duplicates are not even selected.
You can use a PRIMARY KEY or a UNIQUE Index on a table with the appropriate fields to stop duplicate records. Let us take an example – The following table contains no such index or primary key, so it would allow duplicate records for first_name and last_name.
try this:
SELECT DISTINCT * FROM ...
GROUP BY pm.metrics
Try something like these
GROUP BY pos_product_selling.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.
20
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
