|
|
Posted by Bruce Stradling on 08/26/06 22:07
Thanks ... that was exactly what I needed. Here is the final code:
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 then another that removed 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;
"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns982BE98115752Yazorman@127.0.0.1...
> Bruce Stradling (bstradling@cox.net) writes:
> > 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.
>
> If I understand this correctly, you want:
>
> SELECT U.PropertyType, U.Description, U.Status
> FROM UserPropertyTable U
> UNION ALL
> SELECT S.PropertyType, S.Description, S.Status
> FROM SystemPropertyType S
> WHERE S.Status = 'ACTIVE'
> AND NOT EXISTS (SELECT *
> FROM UserPropertyTable U
> WHERE S.Property = U.Property)
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|