[SA 7.5.494]Formatting SQL (INNER/LEFT/RIGHT) JOIN

[SA 7.5.494]Formatting SQL (INNER/LEFT/RIGHT) JOIN

I'm struggle setting a format definition for the way I'd like to set query indentation in joins.
My target is to obtain this:

Code:

SELECT t1.Col1, t1.Col2, t2.Col1, t2.Col2
, t3.Col1, t3.Col2, t4.Col1, t4.Col2
FROM dbo.TableOne AS t1
INNER JOIN dbo.TableTwo AS t2
ON t2.TableTwoKeyCol = t1.TableTwoKeyCol
LEFT JOIN dbo.TableThree AS t3
ON t3.TableThreeKeyCol = t1.TableThreeKeyCol
INNER JOIN dbo.TableFour AS t4
ON t4.TableFourKeyCol = t1.TableFourKeyCol
AND t4.ImportantFlag = 0x1
WHERE t1.TableOneKeyCol = @MyKeyValue
AND t2.TableTwoCode = @MyCodeValue

I tried different way to set the format and ended up with this formatting rule:

	Code:
	SELECT ... AS ..., ... = ... INTO ..., ... FROM ... AS ..., ... INNER JOIN ... ON ... = ... AND ... LEFT JOIN ... ON ... = ... AND ... INNER JOIN ... ON ... = ... AND ... WHERE ... = ... AND ... OR ... GROUP BY ..., ... HAVING ..., ... ORDER BY ..., ...

Which works in that exact case, but if my query becomes :

Code:

SELECT t1.Col1, t1.Col2, t2.Col1, t2.Col2
, t3.Col1, t3.Col2, t4.Col1, t4.Col2
FROM dbo.TableOne AS t1
INNER JOIN dbo.TableTwo AS t2
ON t2.TableTwoKeyCol = t1.TableTwoKeyCol
INNER JOIN dbo.TableFour AS t4
ON t4.TableFourKeyCol = t1.TableFourKeyCol
AND t4.ImportantFlag = 0x1
LEFT JOIN dbo.TableThree AS t3
ON t3.TableThreeKeyCol = t1.TableThreeKeyCol
WHERE t1.TableOneKeyCol = @MyKeyValue
AND t2.TableTwoCode = @MyCodeValue

The result is :

Code:

SELECT t1.Col1, t1.Col2, t2.Col1, t2.Col2, t3.Col1, t3.Col2, t4.Col1, t4.Col2
FROM dbo.TableOne AS t1
INNER JOIN dbo.TableTwo AS t2
ON t2.TableTwoKeyCol = t1.TableTwoKeyCol
INNER JOIN dbo.TableFour AS t4
ON t4.TableFourKeyCol = t1.TableFourKeyCol
AND t4.ImportantFlag = 0x1
LEFT JOIN dbo.TableThree AS t3
ON t3.TableThreeKeyCol = t1.TableThreeKeyCol
WHERE t1.TableOneKeyCol = @MyKeyValue
AND t2.TableTwoCode = @MyCodeValue

Is there a way to have a format that would be compliant with my habits and generic?

Your formatting rule is too narrow, it would only work in one specific case.

Every keyword you have in the pattern is treated a soft anchor that is evaluated for a match before the entire pattern can be applied. It's not like a simple conditional OR combination. So, if your pattern includes both INNER and LEFT joins, it won't match if the statement has just a single join.

I suggest trying separate rules for INNER JOIN and LEFT JOIN and so on. Basically breaking one complex rule for formatting SELECT statements into multiple rules, for example, separate SELECT, separate JOIN, and separate WHERE clauses. Please note that all spacing and offsets are relative to the statement they are applied to, not to the edge of the screen. Hope that works for you.

Thank you for the fast reply

It may not be what you had in mind, but I'm getting close to what I want.
I changed my rule to this:

	Code:
	SELECT ... , ... AS ... , ... = ... INTO ..., ... FROM ... AS ... [INNER] JOIN ... AS ... [LEFT] JOIN ... AS ... [RIGHT] JOIN ... AS ... [FULL] JOIN ... AS ... ON ... = ... AND ... WHERE ... = ... AND ... OR ... GROUP BY ..., ... HAVING ..., ... ORDER BY ..., ...

And I'm getting this as result

Code:

SELECT t1.Col1
, t1.Col2
, t2.Col1
, t2.Col2
, t3.Col1
, t3.Col2
, t4.Col1
, t4.Col2
FROM dbo.TableOne AS t1
INNER JOIN dbo.TableTwo AS t2
ON t2.TableTwoKeyCol = t1.TableTwoKeyCol
LEFT JOIN dbo.TableThree AS t3
ON t3.TableThreeKeyCol = t1.TableThreeKeyCol
INNER JOIN dbo.TableFour AS t4
ON t4.TableFourKeyCol = t1.TableFourKeyCol
AND t4.ImportantFlag = 0x1
WHERE t1.TableOneKeyCol = @MyKeyValue
AND t2.TableTwoCode = @MyCodeValue

and it fully manages the fact to change INNER/LEFT/RIGHT order of appearance without affecting indentation.
My next challenge would be nested joins such as:

	Code:
	SELECT t1.Col1, t1.Col2, t4.Col1, t4.Col2 FROM dbo.TableOne AS t1 RIGHT JOIN dbo.TableFour AS t4 INNER JOIN dbo.TableFive AS t5 ON t5.TableFiveKeyCol = t4.TableFiveKeyCol ON t4.TableFourKeyCol = t1.TableFourKeyCol AND t4.ImportantFlag = 0x1 WHERE t1.TableOneKeyCol = @MyKeyValue AND t2.TableTwoCode = @MyCodeValue