从子查询MySQL中选择列中的值

Question

I am working on two database tables.

Table A contains the name of a lot of products:

_id,
prod_name, 
prod_description,
...

Table B has notes (they are sales):

_id, 
note_num, 
prod_id, 
prod_price, 
quantity,
...

I am looking for some automation in my code

I want to have a select like the following:

SELECT note_num, prod_id, (I want the prod_name from table A), prod_price 
FROM tableB 
WHERE id_ = 1

But, the retriever of data is an array.

I think I missing something in my subquery

(SELECT prod_name FROM tableA WHERE prod_id = prod_id)

My output must be an array like this:

5  | Pink(with id 50) | 40.00
5  | Blue(with id 20) | 50.00
5  | Red (with id 10) | 55.50

Answer 1

Assuming that the tableA key too is prod_id use INNER JOIN

"SELECT note_num, prod_id, tableA.prod_name, prod_price 
 FROM tableB 
 INNER JOIN tableA on tableB.prod_id = tableA.prod_id 
 WHERE id_ = 1"