MySQL Distinct clause issue

Question

I am facing a weird issue with MySQL Distinct clause. I am trying Distinct with inner join but the same term name and object_is is repeated. I want that same name or object_id not repeated.

Edit

In terms table name is repeated and in term_relations terma_taxonomy_id and object is repeated, i want that these values not repeat.

Note: I am working in wordpress

Here is my code:

Select Distinct wTerm.*, wTermTax.*, wTermRel.*
From wp_terms As wTerm
Inner Join wp_term_taxonomy As wTermTax
On wTerm.term_id = wTermTax.term_id
Inner Join wp_term_relationships As wTermRel
On wTermTax.term_taxonomy_id = wTermRel.term_taxonomy_id
Where wTermRel.object_id In(140,99,89,87,58)
And wTermTax.taxonomy = 'pa_garment-type'

How can i achieve this.

A sample of what you're getting back (and some more details about what's wrong with it) would probably be a bit more enlightening. — cHao, Aug 25 '14 at 05:55

score 1 · Answer 1 · answered Aug 25 '14 at 06:13

Distinct selects unique records for you, not unique fields.

If you want field value to be unique, depending on what you are trying to achieve you should either group your records or do several queries, for example i think something like this would work :

SELECT DISTINCT wTerm.name AS name, 
(SELECT DISTINCT terma_taxonomy_id FROM term_relations) AS taxonomy_id,
(SELECT DISTINCT object FROM term_relations) AS object
FROM wp_terms

score 1 · Accepted Answer · edited May 23 '17 at 11:51

As @Logar said distinct will look for a unique record.According to your question what you need is to use Group By clause like this

SELECT column1, column2
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2

where you will use Column1,column2 in group by as the column you want to be uniquely based on. Have alook at this https://stackoverflow.com/questions/2421388/using-group-by-on-multiple-columns.Also note that with group by allows you to do aggregate functions.Ideally you should use distinct if you don't need to perform an aggregate function but in your case to specify the particular unique columns Group By is best applied here.

Select wTerm.*, wTermTax.*, wTermRel.*
From 
wp_terms As wTerm
Inner Join wp_term_taxonomy As wTermTax
On wTerm.term_id = wTermTax.term_id
Inner Join wp_term_relationships As wTermRel
On wTermTax.term_taxonomy_id = wTermRel.term_taxonomy_id
Where 
wTermRel.object_id In(140,99,89,87,58)
And wTermTax.taxonomy = 'pa_garment-type'
Group By 
(Your specific columns here)

MySQL Distinct clause issue

2 Answers2