Not necessarily a SQL Server question...

[Leakyradiator]

Is there any rule of thumb for working through where clauses to prevent cartesian products? I seem to struggle a lot with this one...(particularly when joining more than two tables)

Thanks in Advance

 

[rac2]

Using JOIN syntax should simplify the problem because you will mention each table and the common columns in the JOIN clauses instead of in the WHERE clause.

If you have three tables with primary keys and foreign keys then you need two JOIN clauses.

SELECT column_list
FROM tblA
JOIN tblB ON tblA.PK_col = tblB.FK_col
JOIN tblC ON tblB.PK_col = tblC.FK_col

Of course there are other JOINs and combinations of primary keys and foreign keys or columns that are not keys, but you will always need two JOIN clauses.

The other way to write this is

SELECT column_list
FROM tblA, tblB, tblC
WHERE tblA.PK_col = tblB.FK_col
  AND tblB.PK_col = tblC.FK_col

One rule is that you need n-1 conditions to join n tables.
These n-1 conditions apply to common columns.
Write the joining conditions first followed by any additional conditions.

More complicated combinations of joining and limiting rows fit into this pattern.

SELECT column_list
FROM tblA
JOIN tblB ON tblA.PK_col = tblB.FK_col
             AND tblB = 'this kind'
JOIN tblC ON tblB.PK_col = tblC.FK_col
             AND tblC = 'that kind'
WHERE tblA.col_basic_bunch = 'green_ones'

The WHERE clause for the above would be

SELECT column_list
FROM tblA,tblB,tblC
WHERE tblA.PK_col = tblB.FK_col
  AND tblB.PK_col = tblC.FK_col

  AND tblB = 'this kind'
  AND tblC = 'that kind'

  AND tblA.col_basic_bunch = 'green ones'