Databases
StackOverflow2013
Postsdbo
POUsing subquery data to filter the main SQL query for TableAdapters
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUsing subquery data to filter the main SQL query for TableAdapters
    primarykey
    data
    text
    <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>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJasonMArcher
    1. UScontractorwolf
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload