Complex Queries

[dpatfield66]

In Access (and SQL), I was able to create a UNION Query from two similar tables (one involving Triage Admissions and another involving Admit Admissions)

Basically, I'd create the first query (the UNION) and get a total count of Encounters from both tables.

But some of the encounters were identical and only one was needed.
ex: An Admit record was created from a Triage record. I only needed the Admit, not the triage in the count.

Now, in Access or SQL, I could simply save the first UNION query and then create another query that looked for these Triage duplicates and excluded them from the final count (using a basic NOT IN statement for criteria)
-------------------------------------------------------------------
The PROBLEM is...I'm in .NET using these pesky DataSets, and I'm not sure how they work.

I don't want to save a query in SQL and use it. Not as a view, not as a stored procedure, nothing. I have to work in .NET and only .NET.

So, my first union query is easy. It creates an obixAdmit.cAdmitTable (data table in .NET), and that's great.

So how do I create my second query (eliminating the Triage dupes) using the obixAdmit.cAdmitTable as my reference?

 

[cjard]

note, i forget the completed= 1 in each OR section of the where clause.. homework for you

in oracle we could write something like:

SELECT
  MRNumber,
  PregnancyID,
  Encounter,
  a.Field1,
  t.Field1,
  a.Field2,
  t.Field2
FROM
  OBAdmit a
  FULL OUTER JOIN
  OBTriage t
  ON
    t.MRNumber = a.MRNumber AND
    t.PregnancyID = a.PregnancyID AND
    t.Encounter = a.Encounter AND
    t.Completed = 1 AND
    a.Completed = 1 AND
    a.admitDate >= #01-Jan-2007# AND 
    a.admitDate < #01-Feb-2007# AND
    t.admitDate >= #01-Jan-2007# AND 
    t.admitDate < #01-Feb-2007#

And it would work (items in a join can be joined to constants, also they can be null if it is an outer join)

Try this, if it doesnt work, i'll show you a trick that should