Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2012
    Posts
    15

    Unanswered: Access SQL Question (Nested?)

    Hello,

    I am having a hard time getting a few fairly straightforward SQL statements to combine into a single nested statement, and came here seeking some help.

    I have a single table with Subscription ID's and a secondary field containing either N or R. There are also product ID's that can be 1 - 6. The subscription ID's are occasionally duplicated within the listing.

    The end result that I need is all unique Subscription ID's with the designation N, in one field, and for the second field to show 1 if there is an R Sub ID that matches that N ID, otherwise zero. From either designation, the product ID has to be 1 3 or 5.

    I have been able to do this with three queries, one creates a list of all unique R ID's, the next creates a list of all N IDs (these will be unique by nature), and the third does a left join from the other 2 queries with an iif statement to obtain the final value.

    I have attempted to take these 3 queries and merge them into a single nested query, but I have very little experience with this and am not sure if I am running up against nested limitations, or if the query is just written incorrectly.

    Following are the three queries that I created, which work, and lastly the attempt to nest them, which does not work.

    (Main table here listed as [a])

    Query A:
    Code:
    SELECT DISTINCT [a].[Sub ID]
    FROM [a]
    WHERE ((([a].[N/R])="R") AND (([a].[a])=1 Or ([a].[a])=3 Or ([a].[a])=5))
    Query B:
    Code:
    SELECT [a].[Sub ID]
    FROM [a]
    WHERE ((([a].[N/R])="n") AND (([a].[a])=1 Or ([a].[a])=3 Or ([a].[a])=5));
    Query C:
    Code:
    SELECT [Ind New Subs].[Sub ID], IIf([Ind R Subs].[Sub ID]>0,1,0) AS Duplicates
    FROM [Ind New Subs] LEFT JOIN [Ind R Subs] ON [Ind New Subs].[Sub ID] = [Ind R Subs].[Sub ID];

    Mangled Attempt at the Nested combination:

    Code:
    SELECT 
    	(SELECT [a].[Sub ID] 
    	FROM [a] 
    	WHERE ((([a].[N/R])="n") 
    	AND (([a].[a])=1 Or ([a].[a])=3 Or ([a].[a])=5))) 
    	As Expr1, 
    IIf(
    	(SELECT DISTINCT [a].[Sub ID] 
    	FROM [a] 
    	WHERE ((([a].[N/R])="R") 
    	AND (([a].[a])=1 Or ([a].[a])=3 Or ([a].[a])=5))
    ) >0,1,0) AS Duplicates
    
    FROM 
    	(SELECT [a].[Sub ID] 
    	FROM [a] 
    	WHERE ((([a].[N/R])="n") 
    	AND (([a].[a])=1 Or ([a].[a])=3 Or ([a].[a])=5))) 
    LEFT JOIN 
    	(SELECT DISTINCT [a].[Sub ID] 
    	FROM [a] 
    	WHERE ((([a].[N/R])="R") 
    	AND (([a].[a])=1 Or ([a].[a])=3 Or ([a].[a])=5))) 
    ON 
    	(SELECT [a].[Sub ID] 
    	FROM [a] 
    	WHERE ((([a].[N/R])="n") 
    	AND (([a].[a])=1 Or ([a].[a])=3 Or ([a].[a])=5))) 
    	= 
    	(SELECT DISTINCT [a].[Sub ID] 
    	FROM [a] 
    	WHERE ((([a].[N/R])="R") 
    	AND (([a].[a])=1 Or ([a].[a])=3 Or ([a].[a])=5)));
    Any help would be much appreciated.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Please post the names and definitions (columns names, data type) of the tables or a sample of them (csv is OK).

    You wrote that there is a single table while, from the queries you posted, there are at least two tables involved: [Ind New Subs] and [Ind R Subs].

    Notice that you cannot use an alias (here [a]) if it's not explicitly defined or related to a real table or to a subquery:
    Code:
    SELECT a.Something
    FROM (SELECT TableName.ColumnName AS Something
              FROM  TableName
              GROUP BY ColumnName
             ) AS a
    WHERE ...
    Notice also that the DISTINCT predicate should not be used in this context. The proper method implies a GROUP BY clause.
    Have a nice day!

  3. #3
    Join Date
    Jan 2012
    Posts
    15
    Sorry about that,

    In Query 3, [Ind New Subs] is the name of query b, and [Ind R Subs] is the name of query a. So it queries off of queries A and B to get me my result. I would prefer this result to come from a single query, hence the nesting idea.

    Sub ID is long, N/R is text.

    The [a] field is Integer. In reality, the [a] field and table have different names, I just quickly changed them so that the code is easier to read.

    Thanks for your quick reply.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Like this?
    Code:
    SELECT a.[Subscription ID], 
           a.[N/R], 
           IIf(b.[N/R] = 'R', 1, 0) AS MatchingR
    FROM ( SELECT [Subscription ID], [N/R]
           FROM Subscriptions
           WHERE [Product ID] IN ( 1, 3, 5 ) AND [N/R] = 'N'
           GROUP BY [Subscription ID], [N/R]
         ) AS a LEFT JOIN (
           SELECT [Subscription ID], [N/R]
           FROM Subscriptions
           WHERE [Product ID] IN ( 1, 3, 5 )  AND [N/R] = 'R'
         ) AS b ON
           a.[Subscription ID] = b.[Subscription ID];
    Have a nice day!

  5. #5
    Join Date
    Jan 2012
    Posts
    15
    I have finally had time to look at your reply today and it works perfectly. That little piece of SQL just taught me a lot about the language. I had no idea that you could declare a piece of SQL as an expression and call it within the same code. I have also never encountered IN before. Most most helpful and much appreciated.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •