sql - How to Merge 2 Queries in 1 -

i have these 2 sql queries need join.

query1 (shows list of entiries):

select   e.entityid   ,e.entityno    , e.name   , e.shortname [xx].[xx].[entities] e   e.type = 'a'     , e.yearendmonth = 6 

query2 (shows list of contacts):

select p.firstname,p.lastname, p.email [xxx].[xx].[people] p p.peopleid= (   select top 1 a.peopleid   [xxx].[xx].[entityattentions]   a.entityid = ?   order a.entityattentiontypeid asc ) 

my objective have combined list of entities , contact people.

i tried have error (only 1 expression can specified in select list when subquery not introduced exists)

select   e.entityid   ,e.entityno    , e.name   , e.shortname   ,   (select p.firstname,p.lastname, p.email   [xxx].[xx].[people] p   p.peopleid= (       select  top 1 a.peopleid       [xxx].[xx].[entityattentions]       a.entityid = e.entityid       order a.entityattentiontypeid asc       )   ) [xxx].[xx].[entities] e   e.type = 'a'     , e.yearendmonth = 6; 

i guess must use kind of join don't know do. appreciated.

try this,

select   e.entityid   ,e.entityno    , e.name   , e.shortname     , b.firstname,b.lastname, b.email [xxx].[xx].[entities] e cross apply (     select p.firstname,p.lastname, p.email       [xxx].[xx].[people] p       p.peopleid=          (           select  top 1 a.peopleid           [xxx].[xx].[entityattentions]           a.entityid = e.entityid           order a.entityattentiontypeid asc         )  ) b   e.type = 'a'     , e.yearendmonth = 6;