MS-SQL problem - Only one expression can be specified in the select list

Question

I write the following query:

select 
   id, 
   (select NameEn from [Campaign] where id=CampaignId) as CampaignName,    
   createdDate, 
   (select Name, IdNo, Email, MobileNo from [Members] where id=MemberId) 
from 
   Transactions

and error occurs:
"Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."

How can I rewrite the SQL to make it correct?

Answer 1

You need to use proper (inner|left|...) join syntax.

Something like:

  select 
      t.id, 
      c.NameEn,
      t.createdDate,
      m.Name,
      m.IdNo,
      m.Email,
      m.MobileNo
  from
      [Transactions] t 
      inner join [Campaign] c on c.id = t.CampaignId
      inner join [Members] m on m.id = t.MemberId

Also, in your original code, one of

  select NameEn from [Campaign] where id=CampaignId

or

  select Name,IdNo,Email,MobileNo from [Members] where id=MemberId

might be returning more than one row for each row of [Transactions], which would be illegal.