CODEWITHSUNDEEP
sqlsql-serveroptimizationjoin
Wild Goat
Wild Goat

Reputation: 3579

SQL optimization issue

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!

Upvotes: 3

Views: 82

Answers (3)

DarkTygur
DarkTygur

Reputation: 198

At first glance, it looks like this would do the same thing:

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

Upvotes: 2

aF.
aF.

Reputation: 66687

Try this:

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

Upvotes: 1

Jon Egerton
Jon Egerton

Reputation: 41539

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.

Upvotes: 4

Related Questions