How to simplify following query in one

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to simplify following query in one

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2013-09-17 : 02:26:42

I have following query where i am getting resultset from the same table

Select fd.FieldDependencyId
, fd.FkFieldEnumerationId
, fd.ChildFieldId
, fd.ChildGridId
, fd.ChildMatrixId
, fd.InsertedDate
, fd.InsertedBy
, fd.UpdatedDate
, fd.UpdatedBy
From dbo.FieldDependency fd
Inner Join dbo.FieldEnumeration
On fd.FkFieldEnumerationId = dbo.FieldEnumeration.FieldEnumerationId
Inner Join dbo.SimpleFieldSectionMapper
On fd.ChildFieldId = dbo.SimpleFieldSectionMapper.SimpleFieldSectionMappingId
Where SimpleFieldSectionMapper.FkSourceSectionId = @SourceSectionId
Union
Select fd.FieldDependencyId
, fd.FkFieldEnumerationId
, fd.ChildFieldId
, fd.ChildGridId
, fd.ChildMatrixId
, fd.InsertedDate
, fd.InsertedBy
, fd.UpdatedDate
, fd.UpdatedBy
From dbo.FieldDependency fd
Inner Join dbo.FieldEnumeration
On fd.FkFieldEnumerationId = dbo.FieldEnumeration.FieldEnumerationId
Inner Join dbo.MatrixMasterSectionMapper
On fd.ChildMatrixId = dbo.MatrixMasterSectionMapper.MatrixMasterSectionMappingId
Where MatrixMasterSectionMapper.FkSourceSectionId = @SourceSectionId
Union
Select fd.FieldDependencyId
, fd.FkFieldEnumerationId
, fd.ChildFieldId
, fd.ChildGridId
, fd.ChildMatrixId
, fd.InsertedDate
, fd.InsertedBy
, fd.UpdatedDate
, fd.UpdatedBy
From dbo.FieldDependency fd
Inner Join dbo.FieldEnumeration
On fd.FkFieldEnumerationId = dbo.FieldEnumeration.FieldEnumerationId
Inner Join dbo.GridMasterSectionMapper
On fd.ChildGridId = dbo.GridMasterSectionMapper.GridMasterSectionMappingId
Where GridMasterSectionMapper.FkSourceSectionId = @SourceSectionId

Can i simplify it into one?

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-09-17 : 03:07:41

Yes you could via OR, but you might have performance issues with that. Using UNION or UNION ALL is the alternative to the performance problem of OR.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2013-09-17 : 03:26:12

Ok
Mean's my current implementation is fine?

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2013-09-17 : 08:53:53

Does it run fast enough? If so, you may still want to check the query plan to ensure you have indexes on the right fields etc..

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-17 : 09:04:40

Given that the join conditions are different on the third table, I don't see how they can be made more efficient or combined into one.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-09-17 : 12:49:13

quote:
Originally posted by James K

Given that the join conditions are different on the third table, I don't see how they can be made more efficient or combined into one.

Oops! I skipped right over that.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-17 : 13:03:49

[code]SELECT fd.FieldDependencyId,
fd.FkFieldEnumerationId,
fd.ChildFieldId,
fd.ChildGridId,
fd.ChildMatrixId,
fd.InsertedDate,
fd.InsertedBy,
fd.UpdatedDate,
fd.UpdatedBy
FROM dbo.FieldDependency AS fd
WHERE EXISTS(SELECT * FROM dbo.FieldEnumeration WHERE dbo.FieldEnumeration.FieldEnumerationId = fd.FkFieldEnumerationId)
AND (
EXISTS(SELECT * FROM dbo.SimpleFieldSectionMapper AS x WHERE x.SimpleFieldSectionMappingId = fd.ChildFieldId AND x.FkSourceSectionId = @SourceSectionId)
OR EXISTS(SELECT * FROM dbo.MatrixMasterSectionMapper AS x WHERE x.MatrixMasterSectionMappingId = fd.ChildMatrixId AND x.FkSourceSectionId = @SourceSectionId)
OR EXISTS(SELECT * FROM dbo.GridMasterSectionMapper AS x WHERE x.GridMasterSectionMappingId = fd.ChildGridId AND x.FkSourceSectionId = @SourceSectionId)
);[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-17 : 13:52:36

[code]SELECT fd.FieldDependencyId,
fd.FkFieldEnumerationId,
fd.ChildFieldId,
fd.ChildGridId,
fd.ChildMatrixId,
fd.InsertedDate,
fd.InsertedBy,
fd.UpdatedDate,
fd.UpdatedBy
FROM dbo.FieldDependency AS fd
WHERE EXISTS(SELECT * FROM dbo.FieldEnumeration AS x WHERE x.FieldEnumerationId = fd.FkFieldEnumerationId)
AND (
EXISTS(SELECT * FROM dbo.SimpleFieldSectionMapper AS y WHERE y.SimpleFieldSectionMappingId = fd.ChildFieldId AND y.FkSourceSectionId = @SourceSectionId)
OR EXISTS(SELECT * FROM dbo.MatrixMasterSectionMapper AS z WHERE z.MatrixMasterSectionMappingId = fd.ChildMatrixId AND z.FkSourceSectionId = @SourceSectionId)
OR EXISTS(SELECT * FROM dbo.GridMasterSectionMapper AS t WHERE t.GridMasterSectionMappingId = fd.ChildGridId AND t.FkSourceSectionId = @SourceSectionId)
);[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources