Mysql get different values from same table in one row

Question

Please, correct the title if it is not clear, unfortunately my English in not perfect, and I'm not sure how to explain my problem in one sentence.

I have a table of songs and one of artists, this is the songs table:

|-----------------------------|
| id  | title | artist | date |
|-----------------------------|
| 1   | song1 | 1      | unix |
|-----------------------------|
| 2   | song2 | 1      | unix |
|-----------------------------|
| 3   | song3 | 2      | unix |
|-----------------------------|
| 4   | song4 | 3      | unix |
|-----------------------------|

And this is the artists table

|----------------------|
| id  | name    | date |
|----------------------|
| 1   | artist1 | unix |
|----------------------|
| 2   | artist2 | unix |
|----------------------|
| 3   | artist3 | unix |
|----------------------|

I would like to get a list of 10 random songs and other 3 song titles. I want to make a quiz where users listen to a song and must guess the title, so I need 4 possible answers, the correct and 3 worng titles. So the resoult must be this:

|-------------------------------------------------------------------------|
| id  | title | artist_id | artist_name | date | wrong1 | wrong2 | wrong3 |
|-------------------------------------------------------------------------|
| 1   | song1 | 1         | artist1     | unix | song2  | song3  | song4  |
|-------------------------------------------------------------------------|

How can I reach this? Thank you very much, and please let me know if you need other info.

1) Shown data and shown output does not match. 2) This is PIVOT which is not supported in MySQL - emulate. — Akina, Aug 04 '20 at 13:19
Why are `song3` and `song4` in the same row? They belong to a different artist. — The Impaler, Aug 04 '20 at 13:20
How to chose the 3 wrong songs? (I suppose your table has more than 4 songs.) — jarlh, Aug 04 '20 at 13:23
@Akina output doesn't match because I want to retrieve artist name too with a join... I cannot do this with mysql? :( — simone valentini, Aug 04 '20 at 13:23
@jarlh yes the songs table has more then 4 rows I would like to retrieve wrong titles randomly and different from the right title — simone valentini, Aug 04 '20 at 13:25
@TheImpaler because I would like to make a quiz where users choose between 4 answers, which are titles of different songs, doesn't matter if the artist of wrong answers is not the same — simone valentini, Aug 04 '20 at 13:28

score 0 · Accepted Answer · answered Aug 04 '20 at 13:26

I would suggest that you use two querys. One should return the correct song.

|--------------------------------------------------|
| id  | title | artist_id | artist_name | date |   |
|--------------------------------------------------|
| 1   | song1 | 1         | artist1     | unix |   |
|--------------------------------------------------|

Another could return three random songs

SELECT * FROM table_name
ORDER BY RAND()
LIMIT 3;

|--------------------------------------------------|
| id  | title | artist_id | artist_name | date |   |
|--------------------------------------------------|
| 2   | song2 | 2         | artist2     | unix |   |
| 3   | song3 | 3         | artist3     | unix |   |
| 4   | song4 | 4         | artist4     | unix |   |
|--------------------------------------------------|

Then you could put the four records together in your code.

I though the same, but if I would like to have more then 100 rows, this could need too time... — simone valentini, Aug 04 '20 at 13:30
@simonevalentini I would recommend cycling through the select statements with a loop in your code. I think that would be the best solution. But if this is what you have to do, check out this answer. I think joining two querys is what you are asking for. https://stackoverflow.com/a/10538604/10958914 — Captain, Aug 04 '20 at 13:37

Mysql get different values from same table in one row

1 Answers1