Access SQL Question (Nested?)

ERahn1 · #1 (**permalink**) 02-09-12, 12:06

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.

Sinndho · #2 (**permalink**) 02-09-12, 13:04

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.

ERahn1 · #3 (**permalink**) 02-09-12, 13:21

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.

Sinndho · #4 (**permalink**) 02-09-12, 16:25

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];

ERahn1 · #5 (**permalink**) 02-16-12, 10: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.

Sinndho · #6 (**permalink**) 02-16-12, 10:24

You're welcome!

Internet Services

Web Development

Digital Marketing

Consumer Tech