SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 LEFT JOIN single row from related table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

djpeanut
Starting Member

9 Posts

Posted - 07/25/2008 :  06:49:00  Show Profile  Reply with Quote
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

India
52323 Posts

Posted - 07/25/2008 :  07:57:25  Show Profile  Reply with Quote
1.
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

Go to Top of Page

djpeanut
Starting Member

9 Posts

Posted - 07/26/2008 :  23:53:07  Show Profile  Reply with Quote
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!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 07/27/2008 :  02:06:16  Show Profile  Reply with Quote
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

Edited by - visakh16 on 07/27/2008 02:06:54
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000