MySQL find duplicate of a row and put in a field

Question

I have this table: enter image description here

With this query I'm extracting not activated songs, but I want to add a value (duplicate) if the song has an activated duplicate, how can I do it?

SELECT 
  libs.song_id,
  songs.name AS song_name,
  artists.id AS artist_id,
  artists.name AS artist_name,
  songs.description,
  libs.status,
  libs.activated,
  libs.giftable,
  IF(CONDITION, 1, 0) AS duplicate
FROM libs, songs, artists
WHERE
  libs.song_id = songs.id &&
  artists.id = songs.artist_id &&
  libs.activated = 0 &&
  libs.user_id = '1'
;

Thanks.

EDIT:
I expect to get this:

song_id|song_name|artist_id|artist_name|description|status|activated|giftable|duplicate
1      | "name"  | "id"    | "name"    | "descr"   | 0    | 0       | 1      | 1  
2      | "name"  | "id"    | "name"    | "descr"   | 1    | 0       | 0      | 0

Duplicate of SO MANY Questions, just copy the title and search..... — toesslab, Apr 27 '14 at 07:29
@pc-shooter It's a user library table of activated products and not, not activated productes can be gifted. — Xriuk, Apr 27 '14 at 07:33
Ok I see, Just an idea: two columns as primary key... see here: http://stackoverflow.com/questions/14206868/two-column-as-a-primary-keys-in-mysql — toesslab, Apr 27 '14 at 07:35

score 0 · Answer 1 · answered Apr 27 '14 at 07:41

0

try that :

select song_id , artist_id,status,activated,max(duplicate) duplicate from(

SELECT 
song_id,

artist_id,

status,
activated,
giftable,
if(activated = 1,1 ,0) duplicate
FROM Table1
WHERE


artist_id = '1'

)t
group by song_id

DEMO HERE

answered Apr 27 '14 at 07:41

echo_Me

37,078
5
58
78

I need to get not activated items and search if they have activated duplicates for the same user – Xriuk Apr 27 '14 at 14:23

MySQL find duplicate of a row and put in a field

1 Answers1