FlexibleSearch joins look like SQL joins, but the syntax lives inside curly braces and the "obvious" join column is sometimes wrong — this walks through the syntax and three real examples so the pattern actually sticks.
A FlexibleSearch join wraps the whole FROM clause — including every table, alias, and ON condition — inside one pair of curly braces. Column references use {alias:attribute}, and the join condition always points a foreign-key column at the primary key (or other unique column) it references.
SELECT {p:code}, {p:name}
FROM {Product AS p
JOIN CatalogVersion AS cv ON {p:catalogVersion} = {cv:pk}}
WHERE {cv:version} = 'Online'Category and Product don't reference each other directly — CategoryProductRelation sits between them. You join Product to the relation table, then the relation table to Category, matching each foreign key to the pk it points at.
SELECT {p:code}
FROM {Product AS p
JOIN CategoryProductRelation AS cpr ON {p:pk} = {cpr:target}
JOIN Category AS c ON {cpr:source} = {c:pk}}
WHERE {c:code} = 'cameras'This is the one that trips people up: StockLevel does not store a Product pk — it stores the product's code as a plain string (productCode). So this join matches a code column to a code column, not a pk to a pk, and it still needs a second join to Warehouse to filter by location.
SELECT {s:available}
FROM {StockLevel AS s
JOIN Warehouse AS w ON {s:warehouse} = {w:pk}}
WHERE {s:productCode} = 'ACC-700'
AND {w:code} = 'warehouse_mumbai'A LEFT JOIN keeps every row from the left table even when nothing matches on the right — then WHERE ... IS NULL on a right-side column finds exactly the rows with no match. This is the standard way to find products with no price row, no stock row, or no entry in some other related table.
SELECT {p:code}
FROM {Product AS p
LEFT JOIN PriceRow AS pr ON {p:pk} = {pr:product}}
WHERE {pr:pk} IS NULL