About the output data format after replacing id in one table with corresponding value from another t-CodePudding

My question is based on this topic (link), it was about replacing the id of one table with corresponding value from another table and it works for me, but something went wrong after this. They are my tables below, please notice that there were some differents with those in the link.

name
 ---------- ----------- ----------- 
| name_id  | color_id  | shape_id  |
 ---------- ----------- ----------- 
|    1     |    11     |    01     |
 ---------- ----------- ----------- 
|    2     |    22     |    02     |
 ---------- ----------- ----------- 
|    3     |    33     |    03     |
 ---------- ----------- ----------- 
color
 ---------- ------- 
| color_id | color |
 ---------- ------- 
|    11    |   R   |
 ---------- ------- 
|    22    |   G   |
 ---------- ------- 
|    33    |   B   |
 ---------- ------- 
shape
 ---------- ------- 
| shape_id | shape |
 ---------- ------- 
|    01    |   S   |
 ---------- ------- 
|    02    |   T   |
 ---------- ------- 
|    03    |   C   |
 ---------- -------

and this is the output I wanted to have:

 ---------- ----------- 
|   color  |   shape   |
 ---------- ----------- 
|    R     |     S     |
 ---------- ----------- 
|    G     |     T     |
 ---------- ----------- 
|    B     |     C     |
 ---------- -----------

But this was the output I have had:

 ---------- ----------- 
|   color  |   shape   |
 ---------- ----------- 
| (11,"R") |  (01,"S") |
 ---------- ----------- 
| (22,"G") |  (02,"T") |
 ---------- ----------- 
| (33,"B") |  (03,"C") |
 ---------- -----------

And this is the query code I tried.

SELECT 
   color, shape
FROM 
   name
      INNER JOIN 
         color ON name."color_id" = color."color_id"
      INNER JOIN 
         shape ON name."shape_id" = shape."shape_id"

I was wondering if there are some ways to directly replace the (11,"R") to R , maybe by SQL query syntax or something... or anything I can modify with my existed code. thank you!

CodePudding user response：

in PostgreSQL if you can write table name without fields or without .* then PostgreSQL will be shown to your fields and values in above format, Similar to an array. You must use *. or fields name on select.

SELECT 
   color.*, shape.*
FROM 
   name
      INNER JOIN 
         color ON name."color_id" = color."color_id"
      INNER JOIN 
         shape ON name."shape_id" = shape."shape_id"

Or after . write fields name you need