SQL optimization issue

Question

I have two tables. One table contains information about Assets, another Table about their relation. How could I optimize current query and getting similar results.

SELECT a1.ID FROM Asset a1
WHERE a1.AssetId =
(SELECT r.DestinationAssetId
FROM Relation r
INNER JOIN Asset a2 ON a2.AssetId = r.SourceAssetId
WHERE a2.ID = '1112174' and r.RelationshipType = 'Video File')

Results: 13412331 (ID of Asset which is related to a2.ID = '1125574')

Personally I don't like this stupid sub query, is it any way I can avoid it and optimize this query.

Thanks!

Answer 1

You can lose the subquery:

SELECT dest.ID 
FROM 
Asset src
JOIN Relation r ON src.AssetId = r.SourceAssetId
JOIN Asset dest ON dest.AssetID = r.DestinationAssetID
WHERE src.ID = '1112174' and r.RelationshipType = 'Video File'

Its not much of an optimization performance wise, but it is a little neater.