Merging two tables with selection

Giganews Newsgroups
Subject: Merging two tables with selection
Posted by:  Bruce Stradling (bstradli…@cox.net)
Date: Wed, 23 Aug 2006

I would like to have two tables.  One I call SystemPropertyTypeTable which
contains the defaults and the other UserPropertyTypeTable.  Each has 3
fields.  PropertyType, Description, Status.

The idea here is to allow a user to change his/her defaults or to add a new
Property Type without messing with the system default list.

I would like to Merge these two tables using the following logic.
The SystemPropertyTypeTable any records that have "ACTIVE" for the status.
The UserPropertyTypeTable all records.

Group by Name and remove any duplicates.
if the UserPropertyTypeTable has INACTIVE then Throw away the Active Record
from the SystemPropertyTypeTable  and keep the INACTIVE record.

Here is my code so far.

SELECT T.PropertyType, T.Status
FROM [SELECT PropertyType,Status
FROM  SystemPropertyTypeTable Where Status='ACTIVE'
UNION ALL
SELECT PropertyType,Status
FROM  UserPropertyTypeTable]. AS T
GROUP BY T.PropertyType, T.Status
HAVING (((Count(*))=1));

here is the resultset
  ShowAllRecordsMerged PropertyType Status
      APARTMENT ACTIVE
      APARTMENT INACTIVE
      BUILDING ACTIVE
      GARAGE ACTIVE
      KOISK ACTIVE
      MAINTENANCE SHOP ACTIVE
      MAINTENANCE STORAGE AREA ACTIVE
      OFFICE ACTIVE
      PARKING SPACE ACTIVE
      PARKING SPACE INACTIVE
      SHOP ACTIVE
      STORAGE AREA ACTIVE

So looking at this I would still like to remove any duplicates leaving the
INACTIVE ones which would be the first APARTMENT record and the first
PARKING SPACE record. Also it would be nice to add the description back into
this as well.

Any help anyone can be here would be wonderful.

Thanks in advance.

Bruce

Replies