Merging two tables

26 Aug 2006 2:41 AM

Bruce Stradling

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 (marked in red) 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

26 Aug 2006 5:45 AM

Tom Cooper

Try

Select u.PropertyType, u.Status, u.Description
From UserPropertyTypeTable u
Union All
Select s.PropertyType, s.Status, s.Description
From SystemPropertyTypeTable s
Where s.Status = 'ACTIVE'
And Not Exists (Select 1
From UserPropertyTypeTable u
Where u.PropertyType = s.PropertyType)
Order By PropertyType

Tom

"Bruce Stradling" <bstradl***@cox.net> wrote in message news:5sOHg.4232$_q4.1050@dukeread09...

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 (marked in red) 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

26 Aug 2006 10:10 PM

Bruce Stradling

Hey all ... got my answer on another group from "Erland Sommarskog" <esq***@sommarskog.se> but thought someone might be interested in this too and so the answer is:

SELECT U.PropertyType, U.Description, U.Status
FROM   UserPropertyTypeTable U
UNION ALL SELECT S.PropertyType, S.Description, S.Status
FROM   SystemPropertyTypeTable S
WHERE S.Status = 'ACTIVE'
AND NOT EXISTS (SELECT *
                   FROM   UserPropertyTypeTable U
                   WHERE S.PropertyType = U.PropertyType)
ORDER BY PropertyType;

and to remove all inactive records:

SELECT U.PropertyType, U.Description, U.Status
FROM   UserPropertyTypeTable U
WHERE U.Status = 'ACTIVE'
UNION ALL SELECT S.PropertyType, S.Description, S.Status
FROM   SystemPropertyTypeTable S
WHERE S.Status = 'ACTIVE'
AND NOT EXISTS (SELECT *
                   FROM   UserPropertyTypeTable U
                   WHERE S.PropertyType = U.PropertyType)
ORDER BY PropertyType;

"Bruce Stradling" <bstradl***@cox.net> wrote in message news:5sOHg.4232$_q4.1050@dukeread09...

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 (marked in red) 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