SQL Query Help

  • Thread starter Thread starter Tom Nowak
  • Start date Start date
T

Tom Nowak

I have an ASP.NET Web application that I want to write a query or stored
procedure for.

I want to get the results from two tables. One result set will contain all
the names in one table. The second result set will contain some names from a
second table. Then, I want to display the names that are in the first table
that are NOT in the second table.

Any suggestions would be appreciated.
 
You need to report your framework version.
2.0/3.0 3.5 1.1?

Very different answers depending.
 
I would get an IDataReader with 2 result sets.


Make a strong DataSet or a CustomObject, CustomBusinessCollection.

Let's say its employees.


List<Employee> allEmps = new List<Employee>();

//populate allEmps with first resultset from IDataReader

.......

List<Employee> coolEmps = new List<Employee>();
//populate coolEmps with first resultset from IDataReader


Write a "filter" then loop over allEmps...try to find a match in
coolEmps....
if no match, then add it to
List<Employee> unCoolEmps = new List<Employee>();



http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!140.entry

You can find examples there in the project.

CustomerFilter
CustomerController.SerializeCustomers(method)
will show you how to use the filter.

In the example, I'm using the filter so I can add Order(s) to a specific
Customer's list of Orders.


........

You can write a "not exists" clause in TSQL, but then you're hitting the db
3 times for stuff you can figure out with 2 db hits.
Which I would choose last.



With some good TSQL, you can maybe get the cool list and uncool listh with
one hit.

Let's pretend Germans are cool.

Use Northwind

GO

select

CustomerID ,

CompanyName ,

Country ,

--[IsGerman] =

[IsCool] =

CASE

WHEN Country like '%Germany%' then 1

else 0

END

from dbo.Customers
 
Back
Top