CODEWITHSUNDEEP
sql-serverdatabaset-sqljoinsubquery
Simon
Simon

Reputation: 103

Rewrite a T-SQL query containing subqueries to using joins

I need to select a column from the Application table in the main outer query, which means I have to rewrite the following query to use joins rather than subqueries in order to move the Application table into the scope of the main outer query.

SELECT XMLValue
FROM DynamicField
WHERE ParentID IN (
   SELECT DynamicFieldID
   FROM DynamicField
   WHERE ParentID IN (
      SELECT DynamicFieldID
      FROM Application
      )
   )

Does anyone have a suggestion on how to solve this?

Upvotes: 1

Views: 731

Answers (2)

RAY
RAY

Reputation: 2289

If we don't include result set from a table, we better put it in the where exists portion and NOT to include in the joins.

SELECT XMLValue, app.[your application column name]
FROM DynamicField mainouter
join application app on exists(
    select 1  from  DynamicField appfriend
    where app.DynamicFieldID = appfriend.ParentID
    and appfriend..DynamicFieldID = mainouter.parentID
    )

Upvotes: 1

user5992977
user5992977

Reputation:

So something like this:

SELECT s.*
FROM DynamicField t
INNER JOIN DynamicField t1 on(t.parentID = t1.DynamicFieldID)
INNER JOIN Application s ON(t1.parentID = s.DynamicFieldID)

This will select all from Application table.

Upvotes: 2

Related Questions