Using a SELECT with Subquery as a source for an INSERT.

I have a rather convoluted query to drill down through several tables and return a distinct list  of values (in this case ontractor Id's)

The query is as follows:

SELECT DISTINCT Contractor.ContractorId
FROM	Contractor 
	INNER JOIN ContractorContainer ON Contractor.ContractorID = ContractorContainer.ContractorId
WHERE	ContractorContainer.ContainerId IN
	(SELECT Container.ContainerID
	 FROM	Container 
		INNER JOIN ContainerSizeResolve ON Container.ContainerID = ContainerSizeResolve.ContainerId 
		INNER JOIN WasteType ON ContainerSizeResolve.WasteTypeId = WasteType.WasteTypeId 
		INNER JOIN WasteSubTypeResolve ON WasteType.WasteTypeId = WasteSubTypeResolve.WasteTypeId 
		INNER JOIN WasteSubType ON WasteSubTypeResolve.WasteSubTypeId = WasteSubType.WasteSubTypeID
	WHERE     (WasteSubType.WasteSubTypeID = 67)
	) 
ORDER BY Contractor.ContractorID

Open in new window


It produces a lovely unique list of ContractId's

ContractorId
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...

Open in new window


I now need to do an insert based upon these results that I thought might look something like this:

INSERT INTO ContractorRecycle (ContractorId,WasteSubTypeId) 
VALUES (
	(SELECT DISTINCT Contractor.ContractorId
	FROM	Contractor 
		INNER JOIN ContractorContainer ON Contractor.ContractorID = ContractorContainer.ContractorId
	WHERE	ContractorContainer.ContainerId IN
		(SELECT Container.ContainerID
		 FROM	Container 
			INNER JOIN ContainerSizeResolve ON Container.ContainerID = ContainerSizeResolve.ContainerId 
			INNER JOIN WasteType ON ContainerSizeResolve.WasteTypeId = WasteType.WasteTypeId 
			INNER JOIN WasteSubTypeResolve ON WasteType.WasteTypeId = WasteSubTypeResolve.WasteTypeId 
			INNER JOIN WasteSubType ON WasteSubTypeResolve.WasteSubTypeId = WasteSubType.WasteSubTypeID
		WHERE     (WasteSubType.WasteSubTypeID = 67)
		) 
	),
	67)

Open in new window


Theat just results in a "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated." message.

What am I doing wrong here?

Regards
LVL 2
splantonAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
INSERT INTO ContractorRecycle (ContractorId,WasteSubTypeId)
SELECT DISTINCT Contractor.ContractorId, 67
      FROM      Contractor
            INNER JOIN ContractorContainer ON Contractor.ContractorID = ContractorContainer.ContractorId
      WHERE      ContractorContainer.ContainerId IN
            (SELECT Container.ContainerID
             FROM      Container
                  INNER JOIN ContainerSizeResolve ON Container.ContainerID = ContainerSizeResolve.ContainerId
                  INNER JOIN WasteType ON ContainerSizeResolve.WasteTypeId = WasteType.WasteTypeId
                  INNER JOIN WasteSubTypeResolve ON WasteType.WasteTypeId = WasteSubTypeResolve.WasteTypeId
                  INNER JOIN WasteSubType ON WasteSubTypeResolve.WasteSubTypeId = WasteSubType.WasteSubTypeID
            WHERE     (WasteSubType.WasteSubTypeID = 67)
            )
0
 
splantonAuthor Commented:
Ah! I see... :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.