LEFT JOIN single row from related table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

LEFT JOIN single row from related table

Author

Topic

djpeanut
Starting Member

9 Posts

Posted - 2008-07-25 : 06:49:00

This should be simple but it's giving me a headache...

Say I have a SELECT query on a table t1, returning a number of rows (it's a query for a search page) based on a number of WHERE clauses. No problems there.

However, I also want to include 2 columns from a related table t2. There may be 0 to many related rows in this second table, but I only want to return one row based on ordering by an integer column in t2, or null values if there are no related rows.

I thought about using LEFT JOIN, ordering by the integer column in t2, but the problem is that I also want to be able to sort the SqlDataSource on the page using the databound control's sorting functionality.

Any ideas would be most helpful.

My second question is related - how would I, instead of joining to t1 columns from a single row in a t2, return a COUNT of the number of related rows in t2?

Thanks,

Tom

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-25 : 07:57:25

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

you can use MIN or MAX based on your rule to fetch latest or first information based on IntegerColumn in table 2. LinkCols in table1 & table2 are columns by which they can be joined.

now for second part just do this

2.

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

djpeanut
Starting Member

9 Posts

Posted - 2008-07-26 : 23:53:07

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!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-27 : 02:06:16

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!

then you could do like this

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources