Dex D. Hunter Dex D. Hunter - 1 year ago 62
MySQL Question

How to compare an attribute value in one column with every one in anther column in mysql?

I am new to mysql.
And I have read this and several others on stackoverflow. They all suggest using LIKE but I still cannot get the right result. I wonder if the problem is caused by different rows containing the information.

Suppose I have a talbe

like this:

artist album
Prince Purple Rain
Adam Prince Charming

And I want to select artist
because it appears in both artist and album, I have tried using:
SELECT artist from CD WHERE album LIKE CONCAT('%', artist, '%')

But it kept returning 0 result, and I don't understand why it does not work, anyone can help me?

edit: More information on 'TABLE CD':

artist album
Prince Purple Rain
Adam Prince Charming
James Apple
Furious Banana
Beatles Let It Be
It Come

And it should return artist

Answer Source

This may help you, try this

SELECT artist FROM yourTable WHERE (SELECT GROUP_CONCAT(album) FROM yourTable) LIKE CONCAT('%',artist,'%');

SQL Fiddle

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download