title: Using subquery data to filter the main SQL query for TableAdapters
body: <p>This is basically a SQL question, on how to use data from a subselect to filter your main query.  I am using tableadapters but the question is how to do this in SQL without using joins. i am using tableadapters and ASP.NET as described in this article:</p>
  
  <p><a href="http://msdn.microsoft.com/en-us/library/aa581776.aspx" rel="nofollow">http://msdn.microsoft.com/en-us/library/aa581776.aspx</a></p>
  
  <p>my problem is how to stick to the idea of NOT using joins for my tableadapters (reasons are  discussed in the article).  i have 2 tables: Phones and PhoneAssociations.  the Phones table contains things like phoneID, cellNumber, name, etc.  the PhonesAssociations table contains info about which phones (one to many) are associated with that phone. it contains things like associationID, phoneID, addedByID.  i want to create a query for the tableadapter that returns the phones data (cell, ID, name) that have been added by a single ID.</p>
  
  <p>if i am not filtering the data and want to return all the phones and associated data the sql is simple:</p>
  
  <pre><code>SELECT 
  phoneID, 
  cellNumber,
  providerID, 
  password, 
  nickname,  
  added, 
  (SELECT     
  addedByID
  FROM PhoneAssociations pa
  WHERE p.phoneID = pa.addedByID) AS addedByID
  FROM Phones AS p 
  </code></pre>
  
  <p>but what i cant seem to figure out how to do without using joins is filter that list by the addedByID, the following doesnt work:</p>
  
  <pre><code>SELECT 
  phoneID, 
  cellNumber,
  providerID, 
  password, 
  nickname,  
  added, 
  (SELECT     
  addedByID
  FROM PhoneAssociations pa
  WHERE p.phoneID = pa.addedByID) AS addedByID
  FROM Phones AS p 
  WHERE addedBYID=1
  </code></pre>
  
  <p>this also doesnt work:</p>
  
  <pre><code>WHERE pa.addedBYID=1
  </code></pre>
  
  <p>this is pretty simple using joins, but i am not finding the syntax for doing it using subselects.  any SQL gurus out there that can point me in the right direction?  If you look at the article you will see the need to use subselects instead of joins so that the tableadapters can create your update, delete and insert methods.  Thanks!</p>
  
  <p>the records that need to be returned should retain their original phoneID, i need a list of all the phones (with their phoneID's) that have been added by the phoneID of 12 – contractorwolf 16 mins ago  </p>
  
  <p>i added this in the comments, but maybe i should clarify in the question itself:</p>
  
  <p>if im being more clear, and there are 3 records in the phones table with phoneIDs of 4,8 and 12 and 2 records in the PhoneAssociations table with phoneIDs of 4 and 8 and addedByIDs of 12 what i need to return is 2 records that have phoneIDs of 4 and 8 because they both were addedByID by 12</p>
  
  <pre><code>Phones
  4
  8
  12
  </code></pre>
  
  <p>and </p>
  
  <pre><code>PhoneAssociations (addedByID is second column)
  4, 12
  8, 12
  </code></pre>
  
  <p>what i am trying to answer is: what phoneIDs were added by phoneID 12? thx</p>