SELECT t1.fields,t2.column1,t2.column2 
FROm table1 t1
LEFT OUTER JOIN table2 t2
ON t2.LinkCol=t1.LinkCol
LEFT OUTER JOIN (SELECT LinkCol,{MAX/MIN}(integerCol) AS ReqdValue
                 FROm table2 t2
                 GROUP BY LinkCol) t3
ON t2.LinkCol=t3.LinkCol
AND t2.integerCol=t3.ReqdValue

SELECT t1.fields,COALESCE(t2.RowCount,0)
FROm table1 t1
LEFT OUTER JOIN (SELECT LinkCol,COUNT(PKCol)) AS RowCount
                 FROm table2 t2
                 GROUP BY LinkCol) t2
ON t2.LinkCol=t1.LinkCol

quote:

---

Originally posted by djpeanut

Thanks for 1, it works a treat!

The only problem is that the integer column for ordering is optional, i.e. all rows for a given FK might have this value set to 0 (default) if the user has not specified the ordering integer. In this case, minimum integer value (0) returns several rows. How can I still limit the joined rows to 1 under these circumstances?

Thanks!

---

SELECT t1.fields,t2.column1,t2.column2 
FROm table1 t1
LEFT OUTER JOIN table2 t2
ON t2.LinkCol=t1.LinkCol
LEFT OUTER JOIN (SELECT LinkCol,{MAX/MIN}(CASE WHEN integerCol <> 0 THEN integerCol ELSE NULL END) AS ReqdValue
                 FROm table2 t2
                 GROUP BY LinkCol) t3
ON t2.LinkCol=t3.LinkCol
AND t2.integerCol=t3.ReqdValue