SELECT p.productId,
p.productName,
p.productDescription,
pc.categoryName AS productCategory,
pt.typeName AS productType,
p.productPrice,
pg.gemstoneName AS productGemstone,
pm.materialName AS productMaterial,
p.productWeight,
pi.imageName AS productImageName,
pgg.genderName AS productGender
FROM product p INNER JOIN productcategory pc ON pc.categoryId = p.productCategory
INNER JOIN productgemstone pg ON pg.gemstoneId = p.productGemstone
INNER JOIN productType pt ON pt.typeId = p.productType
INNER JOIN productmaterial pm ON pm.materialId = p.productMaterial
INNER JOIN product_images pi ON pi.productId = p.productId
INNER JOIN productgender pgg ON pgg.genderId = p.productGender
INNER JOIN product_ringsize prs ON prs.productId = p.productId
INNER JOIN product_chainssize pcs ON pcs.productId = p.productId
LEFT JOIN product_pendantsize pps ON pps.productId = p.productId
WHERE pc.categoryLink = 'pendant' AND pi.imageRole ='main';