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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-11-13 : 19:31:35
|
| Jim writes "HelloI too frequently have to write code in a stored procedure that creates a temporary table which is returned to a web page. It seems like I shouldbe able to craft a select statement that would do the work but can't seem to get it correct. Maybe one of you has an idea?I have two tables - master and transactions. There may or may not be transactions for a master record. The transactions table can contain two types of transactions. The two tables are linked on a ID field.The master table looks like:ID = intName = varcharDateEntered = datetimeThe transaction table looks like:ID = intTransType = varcharTransDate = datetimeI want a query to return all the records from the master and any related records from the transactions. Further, I'd like to restrict which master records are returned by the DateEntered and which transactions are returned by type and date.The following query works in part:select * from master as m left join transaction as t on m.id=t.idwhere t.transtype = 'A' and t.TransDate > '01/01/2001'Where ever there is no match between the two tables, NULL values are returned.But that gives me a lot of master records I don't want. When I adda further where clause, as in -select * from master as m left join transaction as t on m.id=t.idwhere m.DateEntered < '01/01/2001' and t.transtype = 'A' and t.TransDate > '01/01/2001'the result set is restricted to only those records that match. No NULL values are returned. I need those unmatched records.Can anyone think how I could rewrite this query so I can restrict which records are returned on the left side of the join and still get NULLs on the right side where there is no matching record?Thanks,Jim Ryan" |
|
|
|
|
|
How to restrict query results on the left side of left join.