SQL Server i need to convert SQL subQuery to SQL JOIN Query . Can anyone help me in this [closed]

nimxete2 于 2023-05-21 发布在其他

关注(0)|答案(1)|浏览(167)

Closed. This question needs details or clarity . It is not currently accepting answers.

Want to improve this question? Add details and clarify the problem by editing this post .

Closed 2 days ago.
This post was edited and submitted for review 2 days ago.
Improve this question

i have two tables (Chain and Location). column ChainId is present in both the tables. I want to display all the columns mentioned in first query plus one additional column(Locations) mentioned in second query , in the output using Join.

select 
        ch.ChainId, ch.ChainCode, ch.Name, ch.IsActive, 
        ch.CreatedBy, ch.CreatedOn, ch.LastUpdatedBy,
        (select count(*) 
         from Location  
         where ChainId = ch.ChainId) as Locations 
    from 
        Chain ch
    where 
        ch.IsActive = 1

sql-server

来源：https://stackoverflow.com/questions/76282343/i-need-to-convert-sql-subquery-to-sql-join-query-can-anyone-help-me-in-this

1条答案

按热度按时间

dxxyhpgq1#

One option that is your safest bet considering the need to aggregate one table but not the other is to move the subquery into your FROM clause and use a LEFT OUTER JOIN:

select 
    ch.ChainId, ch.ChainCode, ch.Name, ch.IsActive, 
    ch.CreatedBy, ch.CreatedOn, ch.LastUpdatedBy,
    lo.Locations 
from 
    Chain ch
    LEFT OUTER JOIN (SELECT count(*) as Locations, ChainID FROM Location GROUP BY ChainID) as lo
        ON lo.ChainID = lo.ChainID
where 
    ch.IsActive = 1

赞(0）回复(0）举报 2023-05-21

我来回答

SQL Server i need to convert SQL subQuery to SQL JOIN Query . Can anyone help me in this [closed]

1条答案

相关问题

热门标签

最新问答