CODEWITHSUNDEEP
mysqlsql
webnoon
webnoon

Reputation: 1005

mysql - join problem

I have two tables like below,

mysql> select * from Books ;
+----+------+------------+----------+----------+
| id | name | author_name| category | category2|
+----+------+------------+----------+----------+
|  1 |    1 | Steve      |    CT001 |    CT003 |
|  2 |    2 | John       |    CT002 |    CT002 |
|  3 |    3 | Larry      |    CT003 |    CT002 |
|  4 |    3 | Michael    |    CT004 |    CT004 |
|  5 | NULL | Steven     |    CT005 |    CT005 |
+----+------+------------+----------+----------+

mysql> select * from Codemst ;
+----+------+------------+
| id | code |    name    |
+----+------+------------+
|  1 | CT001| fiction    |
|  2 | CT002| category1  |
|  3 | CT003| etc        |
|  4 | CT004| etc2       |
|  5 | CT005| etc3       |
+----+------+------------+

I want to get human readable category name when I query like "select * from Books;"
If there was only one category in the Books table, I think I can use "Join" but, in this case what can I do?

Upvotes: 2

Views: 39

Answers (1)

ariel
ariel

Reputation: 16150

select * from Books b 
    Inner Join Codemst c1 on b.category = c1.code 
    inner join codemst c2 on b.category2 = c2.code;

c1.name will hold the readable category, and c2.name the readable category2

Upvotes: 4

Related Questions