MySQL inner join for specific column

Question

I have 2 tables:

table product:

product_id	product_name	price	added_on	modified_on
1	Phone	100	2021-09-25	2021-09-25

table product_image:

id	product_id	product_image1	added_on	modified_on
1	1	phoneimage.jpg	2021-09-27	2021-09-27

I am trying to display product image table in a page with inner join but the problem is that I have same name for added_on and modified_on I want to select from table product product_id, product_name, price and from table product_image all.

So far I have used this query:

SELECT * FROM `product_image` 
INNER JOIN `product` ON product_image.product_id = product.product_id 
ORDER BY `id` DESC

product image display:

Further to the below correct answers, you can shorten your table name references like so: `SELECT t.added_on, nt.added_on ... FROM table t INNER JOIN new_table nt ON t.ref_id = nt.ref_id WHERE t.added_on > ... ` etc. Which can really slim down big queries. So you have ` [table] [t]` in your `FROM` clause and use the shorthand reference in your query. — Martin, Sep 27 '21 at 09:04

score 3 · Answer 1 · answered Sep 27 '21 at 08:57

Don't select all the columns. select columns which you need from any table using tableName.columnName

SELECT 
product.product_id,
product.product_name,
product.price ,
product_image.added_on,
product_image.modified_on 
FROM product_image 
inner join product 
on product_image.product_id = product.product_id 
ORDER BY id DESC

score 1 · Accepted Answer · answered Sep 27 '21 at 08:57

Just use it with the table name as alias:

SELECT product.product_id,
    product.product_name,
    product.price,
    product_image.id,
    product_image.product_id,
    product_image.product_image1,
    product_image.added_on,
    product_image.modified_on
FROM product_image
INNER JOIN product ON product_image.product_id = product.product_id
ORDER BY product_image.id DESC

This way you make sure you take the columns from the table you want to.

MySQL inner join for specific column

2 Answers2