SQL Query Help

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.
 
S

sloan

You need to report your framework version.
2.0/3.0 3.5 1.1?

Very different answers depending.
 
S

sloan

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top