Joining Table with Multiple Columns

coopni

Hi, I am trying to achieve the following SQL and I having problems trying to understand how to do the LEFT JOIN specifically with the PHP editor. If it was just one column to join it is straight forward, but I cannot see how to do the syntax when it comes to adding an AND on other columns.

->leftJoin('GemPrice', 'GemPrice.GemTypeId ', '=', 'JewelleryProductDetails.GemId')

SELECT p.Code, p.Price, (gp.CostEachUSD*23000) FROM Product p
JOIN JewelleryProductDetails jpd ON jpd.ProductId = p.Id
LEFT JOIN GemPrice gp ON gp.GemTypeId = jpd.GemId AND gp.GemCutId = jpd.GemCut AND gp.GemSizeId = jpd.GemSize;

allan

This is the bit of the manual you need.

Basically you can do:

->leftJoin( 'GemPrice gp', 'gp.GemTypeId = jpd.GemId AND gp.GemCutId = jpd.GemCut AND gp.GemSizeId = jpd.GemSize')

to get what you are looking for.

Allan