0

I am trying to build SQL query to select image column, where product_id is coming from 2 different table.

Here is my database schema:

enter image description here

I want to select images of products from ixml_prd_map.id_oc_prd. I need to get from here to product_image.image.

On the picture you can see what I am trying to do, here is what I done so far, but it's not working. The error:

Not unique table/alias: 'product'


SELECT product_image.image FROM (product INNER JOIN ixml_prd_map ON product.sku = ixml_prd_map.id_oc_prd) INNER JOIN product ON product_image.product_id = product.product_id
Adrian
  • 2,576
  • 9
  • 49
  • 97

2 Answers2

1

You have to do the join with product_image

SELECT 
product_image.image 
FROM product 
INNER JOIN ixml_prd_map ON product.sku = ixml_prd_map.id_oc_prd
INNER JOIN product_image ON product_image.product_id = product.product_id
Abhik Chakraborty
  • 44,654
  • 6
  • 52
  • 63
  • Superb, thank you. One more qestion: How I can delete results? I tried to replace SELECT with DELETE, but I get error Unknown table in MULTI DELETE. – Adrian May 28 '15 at 20:13
  • You need to use join while deleting for example for the above select if you want to delete from product table it will be as `delete p from product p join ixml_prd_map i on i.id_oc_prd = p.sku join product_image pi on pi.product_id = p.product_id` However if you create a question with what exactly to be done it would be easier to provide a solution. – Abhik Chakraborty May 29 '15 at 05:55
1

You actually join the table product more than once.

In that case, you must give it an alias.

Example:

SELECT *
FROM       product product1
...
INNER JOIN product product2
...

Example by your provided code:

SELECT     product_image.image
FROM       product product1
INNER JOIN ixml_prd_map
ON         product1.sku = ixml_prd_map.id_oc_prd
INNER JOIN product product2
ON         product2.product_id = product_image.product_id
Daniel W.
  • 31,164
  • 13
  • 93
  • 151