Help with query: If was column copied, keep original out of select.

Question

So I have this table that holds these 'assets', there are say 25 'special asset's that can not be edited by the users because they are shared. However as a way to allow the users to edit the asset and have their own version it makes a copy of the original that they are then allowed to edit. The row in the table holds a value called OriginalAssetID once it is copied (otherwise it is zero). Now the hang up: We don't want the original asset to show up any more for that user when browsing assets. Once they make the personal copy of the original asset only their new personal one should show not both. So how can I make a query that says get all these assets, but don't get the ones that have an assetID that falls into one of the selected assets OriginalAssetID column? Keep in mind I cannot flag anything on the original asset itself because it is shared by other users who have not made a copy yet will still see it in their browse list. GOOD LUCK I HAVE BEEN TRYING TO FIGURE THIS OUT FOR HOURS.

(SQL Server 2005)

TO ADD SOME EXAMPLE:

asset one: asset_id = 5 orig_id = 0

asset two (is a copy of 1): asset_id = 12 orig_id = 5

so in this case we want to get back asset two only and no longer get back asset one because it is now copied and the copied version should be the live one, but we can't get rid of it because some other users may not have copied it yet thus for them they want to have asset one still

Answer 1

This hasn't been tested, but I believe it would work.

SELECT * FROM Assets WHERE user_id = 99 AND asset_id NOT IN 
(SELECT original_asset_id FROM Assets WHERE user_id = 99 AND original_asset_id <> 0)

To clarify, I'm picturing the following table structure:

Assets
------
asset_id (PK)
user_id
original_asset_id (FK)

Answer 2

what about

   Select IsNull(p.AssetId, o.AssetId) AssetId,
          IsNull(p.AssetName, o.AssetName) AssetName,
          IsNull(p.AssetAge, o.AssetAge) AssetAge
      -- etc.
   From Assets o
      Left Join Assets p
         On p.OrigAssetId = o.AssetId
            And p.userId = @MyUserId

In Select clause, just add all columns you want to output... For each one, if there is a user specific row in the table, then the IsNull will output the value from that outer joined table, if not then the first parameter in each IsNull will be null, and all the IsNulls will output the default value from the non-user specific row...

Answer 3

This is SQL but you can easily translate it to Linq.

SELECT * 
FROM Assets 
WHERE user_id = @user_id
UNION ALL
SELECT * FROM Assets 
WHERE 
   original_asset_id = 0
   and asset_id NOT IN 
      (SELECT original_asset_id FROM Assets WHERE user_id = @user_id)

The first select returns all assets overridden by user. Second select returns all assets that are not overridden by user.

Answer 4

this should do it in linq:

var assets = new object[]{}; //your assets enumerable
var userAsserts = (from asset in assets
                  select new {Id = asset.OriginalAssetID == 0 ? asset.ID : asset.OriginalAssetID, Asset= asset).OrderByDescending(a => a.OriginalAssetID).Distinct(a => a.Id);

Answer 5

I believe 'not exists' will work in this situation:

select asset_id
from assets a
where not exists (select * from assets where orig_asset_id = a.asset_id)