sql - How to get data with multiple references to single table -

i have image table 3 columns: id, name, , refid - contain id other table (with example category table , product table). category table 3 columns: id, name , productid. product table includes 3 columns: id, name. product's data:

id                                     name     659543d6-1a0d-4151-b94c-f44ccda752b5   product1 5c1a5a2f-4579-41d2-8fae-93ef71451285   product2      2c35a651-1681-4e5e-9f0c-315169bdfa1d   product3 

and category's data:

id                                     name        productid      f3fff612-1d6d-4447-b472-2486c4893cdd   category1   659543d6-1a0d-4151-b94c-f44ccda752b5 4a2ff39d-13e0-4365-8dea-7fad6d6df405   category2   659543d6-1a0d-4151-b94c-f44ccda752b5 599aea59-8a85-409c-9026-7e4313af523d   category3   659543d6-1a0d-4151-b94c-f44ccda752b5 

image's data:

id                                     name     refid      bdd396a2-37a7-4b20-b8c5-8ef2cd58d4c0   image1   659543d6-1a0d-4151-b94c-f44ccda752b5 7cda01ff-b98a-4cdc-a92b-0e31a9cdcd20   image2   f3fff612-1d6d-4447-b472-2486c4893cdd 

please see data example: image1 image of product , image2 image of category. , query:

    select category.id, category.name, product.name, image.name      category      inner join product     on category.productid = product.id     inner join image     on category.id = image.refid  -- on product.id = image.refid  

with query image2 or image1. problem how 2 image 1 query?

are looking this?

select c.id category_id,         c.name category_name,         p.name product_name,         i1.name category_image,         i2.name product_image   category c join product p     on c.productid = p.id left join image i1      on c.id = i1.refid left join image i2     on p.id = i2.refid  

output:

 |                          category_id | category_name | product_name | category_image | product_image | -------------------------------------------------------------------------------------------------------- | f3fff612-1d6d-4447-b472-2486c4893cdd |     category1 |     product1 |         image2 |        image1 | | 4a2ff39d-13e0-4365-8dea-7fad6d6df405 |     category2 |     product1 |         (null) |        image1 | | 599aea59-8a85-409c-9026-7e4313af523d |     category3 |     product1 |         (null) |        image1 | 

here sqlfiddle demo