Dex D. Hunter Dex D. Hunter - 5 days ago 7
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

CD
like this:

artist album
Prince Purple Rain
Adam Prince Charming


And I want to select artist
Prince
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
Prince
,
It

Answer

This may help you, try this

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

SQL Fiddle

Comments