CODEWITHSUNDEEP
sqlsql-serverjoinllblgenpro
SupaOden
SupaOden

Reputation: 742

Joining SQLsingle query

select * from ProjectFeature pf
inner join ProjectArea pa
on pa.AreaId = pf.AreaId
inner join Project p
on p.ProjectId = pa.ProjectId
where (p.ProjectId=175 AND pf.FeatureStatusTypeId=2
AND (pf.FeatureId IN (SELECT dbo.[MaxFeatureVersoinId](pf.FeatureId) AS [FeatureId])) AND (pf.FeatureId IN (SELECT dbo.[MaxFeatureVersoinId](pf.FeatureId) AS [FeatureId])))

I have another query

select * from ProjectFeature pf
inner join ProjectArea pa
on pa.AreaId = pf.AreaId
inner join Project p
on p.ProjectId = pa.ProjectId
where (p.ProjectId=175 AND pf.FeatureStatusTypeId=2
AND (pf.FeatureId IN (SELECT dbo.[MaxFeatureVersoinId](pf.FeatureId) AS [FeatureId])) AND (pf.FeatureId IN (SELECT dbo.[MaxFeatureVersoinId](pf.ChangeRequestToFeatureId) AS [FeatureId])))

the second query is exactly the same, but the function takes another field at the very end of the query

I have tried UNION Operator but LLBLGEN pro doesnt support UNION operator How can I make this int o a single query that retrieves both the resulsets?

Upvotes: 1

Views: 158

Answers (3)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

select * from ProjectFeature pf
inner join ProjectArea pa
on pa.AreaId = pf.AreaId
inner join Project p
on p.ProjectId = pa.ProjectId
where (p.ProjectId=175 AND pf.FeatureStatusTypeId=2
AND ((pf.FeatureId IN (SELECT dbo.[MaxFeatureVersoinId](pf.FeatureId) AS [FeatureId])) 
OR (pf.FeatureId IN (SELECT dbo.[MaxFeatureVersoinId](pf.ChangeRequestToFeatureId) AS [FeatureId]))))

Having removed redundance it is possible to reduce request

Upvotes: 1

Taryn
Taryn

Reputation: 247860

This is kind of a strange way to rewrite the query, but you can also try something like this:

select * 
from ProjectFeature pf
inner join ProjectArea pa
  on pa.AreaId = pf.AreaId
inner join Project p
  on p.ProjectId = pa.ProjectId
where 
(
  p.ProjectId=175 
  AND pf.FeatureStatusTypeId=2
  AND (pf.FeatureId IN (SELECT dbo.[MaxFeatureVersoinId](pf.FeatureId) AS [FeatureId])) 
  AND (pf.FeatureId IN (SELECT dbo.[MaxFeatureVersoinId](pf.FeatureId) AS [FeatureId]))
)
OR
(
  p.ProjectId=175 
  AND pf.FeatureStatusTypeId=2
  AND (pf.FeatureId IN (SELECT dbo.[MaxFeatureVersoinId](pf.FeatureId) AS [FeatureId])) 
  AND (pf.FeatureId IN (SELECT dbo.[MaxFeatureVersoinId](pf.ChangeRequestToFeatureId) AS [FeatureId]))
)

Place the two WHERE clauses in the same query with an OR between them. Also in your first query you appear to be repeating the last line, I am not sure why you need to do that.

Upvotes: 0

Praveen Kumar Purushothaman
Praveen Kumar Purushothaman

Reputation: 167220

If you want to append the results as another row(s) under the same query, provided that they have exactly same columns and datatypes, then you can use UNION for the same:

select * from ProjectFeature pf
inner join ProjectArea pa
on pa.AreaId = pf.AreaId
inner join Project p
on p.ProjectId = pa.ProjectId
where (p.ProjectId=175 AND pf.FeatureStatusTypeId=2
AND (pf.FeatureId IN (SELECT dbo.[MaxFeatureVersoinId](pf.FeatureId) AS [FeatureId])) AND (pf.FeatureId IN (SELECT dbo.[MaxFeatureVersoinId](pf.FeatureId) AS [FeatureId])))
UNION
select * from ProjectFeature pf
inner join ProjectArea pa
on pa.AreaId = pf.AreaId
inner join Project p
on p.ProjectId = pa.ProjectId
where (p.ProjectId=175 AND pf.FeatureStatusTypeId=2
AND (pf.FeatureId IN (SELECT dbo.[MaxFeatureVersoinId](pf.FeatureId) AS [FeatureId])) AND (pf.FeatureId IN (SELECT dbo.[MaxFeatureVersoinId](pf.ChangeRequestToFeatureId) AS [FeatureId])))

Upvotes: 0

Related Questions