title: joining two select queries results in one row for each value?
body: <p>I have a table called <code>CharUser</code> has the following data</p>
  
  <pre><code>UserID UserName UserPhone UserAddress
  </code></pre>
  
  <p>also a table called <code>subscriptionjob</code></p>
  
  <pre><code>subID userId   companyid
  </code></pre>
  
  <p>another table called user <code>UsersCvs</code> has the following data</p>
  
  <pre><code>CVId   Companyuserid   cvpath
  </code></pre>
  
  <p>I want to do a query that result as the following for each row</p>
  
  <pre><code>userid   username   userphone  useraddress usercv
  </code></pre>
  
  <p>the userid is given from the <code>subcriptionjob</code> table
  i tried the following
  the first query </p>
  
  <pre><code>SELECT        CharUserId, CharUserName, CharUserPassword, CharUserMail, CharUserPhone, CharUserAddress, CharUserGender, ProgId, CharUserBirthdate
  FROM            CharUser
  WHERE        (CharUserId IN
                               (SELECT        CompanyUserId
                                  FROM            SubsciptionJob
                                  WHERE        (JobId = 1)))
  </code></pre>
  
  <p>and this the second query </p>
  
  <pre><code>SELECT        TOP (1) CvPath
  FROM            UsersCvs
  WHERE        (CompanyUserId IN
                               (SELECT        CompanyUserId
                                  FROM            SubsciptionJob AS SubsciptionJob_1
                                  WHERE        (JobId = 1)))
  ORDER BY CvId DESC
  </code></pre>
  
  <p>Both work alone just fine
  i tried to combine them putting union in between but it doesn't work</p>
  
  <p>i want to attach the cvpath column to the first query with a unique value for each row</p>
  
  <p>any ideas.. ?</p>
  
  <p>thank you</p>