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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.