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

The idea here is to allow a user to change his/her defaults or to add a =
new=20
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=20
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=3D'ACTIVE'
UNION ALL
SELECT PropertyType,Status
FROM UserPropertyTypeTable]. AS T
GROUP BY T.PropertyType, T.Status
HAVING (((Count(*))=3D1));

here is the resultset
ShowAllRecordsMerged=20
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=20
INACTIVE ones (marked in red) which would be the first APARTMENT record =
and the first=20
PARKING SPACE record. Also it would be nice to add the description back =
into=20
this as well.

Any help anyone can be here would be wonderful.

Thanks in advance.

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 =3D 'ACTIVE'
And Not Exists (Select 1
From UserPropertyTypeTable u
Where u.PropertyType =3D s.PropertyType)
Order By PropertyType

Tom

[quoted text, click to view]
I would like to have two tables. One I call SystemPropertyTypeTable =
which=20
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=20
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=20
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=3D'ACTIVE'
UNION ALL
SELECT PropertyType,Status
FROM UserPropertyTypeTable]. AS T
GROUP BY T.PropertyType, T.Status
HAVING (((Count(*))=3D1));

here is the resultset
ShowAllRecordsMerged=20
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=20
INACTIVE ones (marked in red) which would be the first APARTMENT =
record and the first=20
PARKING SPACE record. Also it would be nice to add the description =
back into=20
this as well.

Any help anyone can be here would be wonderful.

Thanks in advance.

Hey all ... got my answer on another group from "Erland Sommarskog" =
<esquel@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 =3D 'ACTIVE'
AND NOT EXISTS (SELECT *=20
FROM UserPropertyTypeTable U
WHERE S.PropertyType =3D U.PropertyType)
ORDER BY PropertyType;

and to remove all inactive records:

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

[quoted text, click to view]
I would like to have two tables. One I call SystemPropertyTypeTable =
which=20
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=20
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=20
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=3D'ACTIVE'
UNION ALL
SELECT PropertyType,Status
FROM UserPropertyTypeTable]. AS T
GROUP BY T.PropertyType, T.Status
HAVING (((Count(*))=3D1));

here is the resultset
ShowAllRecordsMerged=20
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=20
INACTIVE ones (marked in red) which would be the first APARTMENT =
record and the first=20
PARKING SPACE record. Also it would be nice to add the description =
back into=20
this as well.

Any help anyone can be here would be wonderful.

Thanks in advance.