CODEWITHSUNDEEP
mysql
Xriuk
Xriuk

Reputation: 392

MySQL assign var in SELECT but not display it

I have this query:

SET @id := '';
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,
  @id := libs.song_id,
  IF((SELECT activated FROM libs WHERE @id = song_id && activated = 1), 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';

Everything goes as planned but I get also a column named @id := libs.song_id, how can I avoid it?
Thanks.

Upvotes: 1

Views: 120

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

A few things first. Learn to use proper join syntax. A simple rule: Just don't use commas in the from clause.

Second, you don't even have to initialize the variable as a separate statement.

Third, the idea is simple. Just assign the variable in another expression and there is a very natural place to do it:

SELECT (@id := libs.song_id) as 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((SELECT activated FROM libs WHERE @id = song_id AND activated = 1), 1, 0) AS duplicate
FROM libs join
     songs
     on libs.song_id = songs.id join
     artists
     on artists.id = songs.artist_id
WHERE libs.activated = 0 AND libs.user_id = '1';

Upvotes: 1

Related Questions