CODEWITHSUNDEEP
mysqldatabasejoin
JonZee
JonZee

Reputation: 27

Change ID values in the result set of a mysql join

I have 2 tables, books and genre_order. I'm trying to select all values from books(t1) and order them by genre using order column from genre_order(t2) using a JOIN. The result set orders them just fine, however, I'm in the dark about how to change book_id to reflect the new order.

table : books
+-------+------------------+-----------+
|book_id|   name           |   genre   |
+-------+------------------+-----------+
|   1   |   Harry Potter   |  Fantasy  |
|   2   |   The Alchemist  |  Fantasy  |
|   3   |   Inferno        |  Thriller |
|   4   |   Steve Jobs     |  Biography|
|   5   |   John Adams     |  Biography|
+-------+------------------+-----------+


table : genre_order
+-----------+-------+
|   genre   | order |
+-----------+-------+
| Biography |  1    |
| Fantasy   |  2    |
| Thriller  |  3    |
+-----------+-------+

Query:

SELECT * FROM books t1 JOIN genre_order t2 ON t1.genre = t2.genre ORDER BY t2.order ASC

result:

  +-------+------------------+-----------+-----------+------+
  |book_id|   name           |   genre   |  genre    | order|
  +-------+------------------+-----------+-----------+------+
  |   4   |   Steve Jobs     |  Biography|  Biography|  1   |
  |   5   |   John Adams     |  Biography|  Biography|  1   |
  |   1   |   Harry Potter   |  Fantasy  |  Fantasy  |  2   |
  |   2   |   The Alchemist  |  Fantasy  |  Fantasy  |  2   |
  |   3   |   Inferno        |  Thriller |  Thriller |  3   |
  +-------+------------------+-----------+-----------+------+

Expected result:

  +-------+------------------+-----------+------+
  |book_id|   name           |   genre   | order|
  +-------+------------------+-----------+------+
  |   1   |   Steve Jobs     |  Biography|  1   |
  |   2   |   John Adams     |  Biography|  1   |
  |   3   |   Harry Potter   |  Fantasy  |  2   |
  |   4   |   The Alchemist  |  Fantasy  |  2   |
  |   5   |   Inferno        |  Thriller |  3   |
  +-------+------------------+-----------+------+

Is it possible to change book_id in the result set?. Also, how do I avoid redundant columns?

Upvotes: 0

Views: 42

Answers (2)

Tushar
Tushar

Reputation: 568

Try this code 

SELECT ROW_NUMBER() over (order by pk_field ) as book_id,t1.name,t2.genre,t2.order FROM books t1 JOIN genre_order t2 ON t1.genre = t2.genre ORDER BY t2.order ASC

Or This code

select  @ab:=@ab+1 as book_id,a.* from (SELECT t1.name,t2.genre,t2.order FROM books t1 JOIN genre_order t2 ON t1.genre = t2.genre ORDER BY t2.order DESC) a, (SELECT @ab:= 0) AS ab

Upvotes: 1

Manash Kumar
Manash Kumar

Reputation: 1085

Use a variable as a table and cross join it with the source table:

SELECT @row_number:=@row_number+1 AS book_id, t1.* 
FROM books t1, (SELECT @row_number:=0) AS t 
JOIN genre_order t2 ON t1.genre = t2.genre 
ORDER BY t2.order ASC

Upvotes: 0

Related Questions