MYSQL: not unique values

I have a table something like this

CREATE TABLE wp_postmeta	
(		
	 meta_id INT	
	,post_id INT	
	,meta_key VARCHAR(1000)	 
	,meta_value VARCHAR(1000)
)
INSERT INTO wp_postmeta VALUES
(1	,123	,'keyword;'	,'xyz,abc,Something Else,Not Unique'),
(2	,124,'keyword;'	,'xyz,abc,Something Else,Not Unique'),
(3	,125	,'keyword;'	,'Experts, Exchange,Is Great,and Unique '),
(4	,123	,'Other_key;'	,'Hello World'),

Open in new window



I need to find all the post_id where meta_key = 'keyword' and meta_value isn't  unique
 

Any Ideas?
LVL 1
trevor1940Asked:
Who is Participating?
 
Pawan KumarDatabase ExpertCommented:
Please try this -

SELECT CONCAT(w.post_id,'|',w.meta_key,'|', w.meta_value) output FROM
(
	select meta_value,MIN(meta_key) meta_key
	from wp_postmeta 
	where meta_key = 'keyword;'
	GROUP BY meta_value 
	HAVING COUNT(*) > 1
)k INNER JOIN wp_postmeta w ON w.meta_key = k.meta_key
AND w.meta_value = k.meta_value
 

Open in new window


OUTPUT

/*------------------------
OUTPUT 
------------------------*/
output
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
123|keyword;|xyz,abc,Something Else,Not Unique
124|keyword;|xyz,abc,Something Else,Not Unique

(2 row(s) affected)

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
What is the output you need from the input given.
0
 
trevor1940Author Commented:
123|keyword|xyz,abc,Something Else,Not Unique
124|keyword|xyz,abc,Something Else,Not Unique

Open in new window


Used a "|" to separate colums as meta_value is comer separated list
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Olaf DoschkeSoftware DeveloperCommented:
I think your input data needs the minor correction of last post_id 126, though it wouldn't matter as far as I interpret your task deescription.

Since you want unique mata_value per meta_key you can group by both, pick out groups HAVING COUNT(*)=1 and then take MIN(post_id) as post_id, because
a) you can't simply select post_id itself, as you don't group by it, but
b) in groups with one member only MIN(field)=MAX(field)=field, so needing to aggregate doesn't pose a problem to get at the single row value.

Select MIN(post_id) as post_id FROM wp_postmeta GROUP BY meta_key, meta_value HAVING COUNT(*)=1

Open in new window


If that doesn't fit play around with http://sqlfiddle.com/#!9/a4a193/1/0

Bye, Olaf.
0
 
Olaf DoschkeSoftware DeveloperCommented:
Sorry, forget my previous answer, that crossed with your explanation. I thought the opposite.

This will work for your problem:
Select post_id FROM wp_postmeta INNER JOIN 
(Select meta_key, meta_value FROM wp_postmeta GROUP BY meta_key, meta_value HAVING COUNT(*)>1) tmp
ON  tmp.meta_key = wp_postmeta.meta_key 
AND tmp.meta_value = wp_postmeta.meta_value

Open in new window


http://sqlfiddle.com/#!9/a4a193/3/0
1
 
trevor1940Author Commented:
Thanx will try in the morning
0
 
Pawan KumarDatabase ExpertCommented:
welcome.
0
 
trevor1940Author Commented:
@Olaf
I need to use a where clause because each post has multiple key value pairs some are legitimately the same like  Author = Trevor

This fetches zero rows
Select post_id FROM wp_postmeta INNER JOIN 
(Select meta_key, meta_value FROM wp_postmeta where meta_key = 'keyword' GROUP BY meta_key, meta_value HAVING COUNT(*)>1 ) tmp
ON  tmp.meta_key = wp_postmeta.meta_key 
AND tmp.meta_value = wp_postmeta.meta_value

Open in new window


http://sqlfiddle.com/#!9/305ad/1
0
 
Pawan KumarDatabase ExpertCommented:
Any update on my code?
0
 
trevor1940Author Commented:
@Olaf my bad not spotted 'keyword;' typo  hence my comment however I did need all 3 columns like  @Pawan     but with out the CONCAT "|" in the example was only supposed to  highlight where columns  sepperated

Thanx for your help
0
 
Olaf DoschkeSoftware DeveloperCommented:
Simply look at the sqlfiddle link. Also, the query you reposted wasn't mine.

Bye, Olaf.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.