Using SQL result variables in Nested SELECT statement

Question

I'm really unsure if the title of this is what I am trying to say, but it's the closest I could come. What I want to do is take the initial results from a query and use them in a nested Select statement within an INSERT statement. Here is my example: Here is my initial query which gives me what I am looking for an works correctly;

    SELECT Min(Inquirer.ID) as MinID, Max(Inquirer.ID) as MaxID, 
    Min(Inquirer.Program) as MinProg, Max(Inquirer.Program) as MaxProg,
     COUNT(Inquirer.Name) AS NumOccurrences
    FROM Inquirer
    GROUP BY Inquirer.Name
    HAVING ( COUNT(*) = 2 )

The results display like this;

MinID         MaxID           MinProg          MaxProg          NumOccurrences
 27             81            Pavilion         Appleton              2

I would like to use the MinID and MaxID values that I retrieve in a corresponding INSERT with a nested SELECT something like this;

Insert into Transfer(InquiryID_fk, ReferElsewhere, ReferMcLean, ReferExternal, ReferredFrom, TransferInquiryID_fk)
Values(SELECT Inquiry.ID from Inquiry 
Where Inquiry.InquirerID_fk = MinID,'Yes',MaxProg,NULL,MinProg,
SELECT Inquiry.ID from Inquiry WHERE Inquiry.InquirerID_fk = MaxID)

And that is really it in a nutshell, not sure if it can be accomplished or how to accomplish it, would I have to make a temp table beforehand and loop through that to get the values? Any help is appreciated, thank you in advance,

NickG

score 2 · Answer 1 · answered Aug 01 '12 at 19:55

Don't use VALUES in this case, use straight INSERT INTO ( )... SELECT.

E.g.

INSERT INTO Transfer(InquiryID_fk, ReferElsewhere, ReferMcLean, ReferExternal, ReferredFrom, TransferInquiryID_fk)
SELECT Min(Inquirer.ID), 'Yes', Max(Inquirer.Program), NULL,     Min(Inquirer.Program), Max(Inquirer.ID)
    FROM Inquirer
    GROUP BY Inquirer.Name
    HAVING ( COUNT(*) = 2 )

score 1 · Answer 2 · edited Aug 01 '12 at 20:12

1

Try with this code

Insert into Transfer(
InquiryID_fk, 
ReferElsewhere,
 ReferMcLean,
 ReferExternal, 
ReferredFrom, 
TransferInquiryID_fk)
SELECT Min(Inquirer.id), `yes`,Max(Inquirer.Program), null, Min(Inquirer.Program), Max(Inquirer.ID)
FROM Inquirer
GROUP BY Inquirer.Name
HAVING ( COUNT(*) = 2 )

edited Aug 01 '12 at 20:12

answered Aug 01 '12 at 20:01

HouseDoctorized

71
8

score 0 · Answer 3 · edited Aug 02 '12 at 09:42

Using a new table solve your problem, but I would not recommend since will add too many nested selects to your query, which may reduce the performance.

In your case I would create a stored procedure for this

Create a stored procedure for this with two variables: MinId and MaxId
In your first select (which you gets the minid and maxId) you set the values of these variables

Them, in your query, use these variables:

Insert into Transfer(InquiryID_fk, ReferElsewhere, ReferMcLean, ReferExternal, ReferredFrom, TransferInquiryID_fk)
 SELECT Inquiry.ID from Inquiry Where Inquiry.InquirerID_fk = @MinID
 ,'Yes'
 ,MaxProg
 ,NULL
 ,MinProg
 ,SELECT Inquiry.ID from Inquiry WHERE Inquiry.InquirerID_fk = @MaxID

podiluska · Answer 4 · 2012-08-02T14:19:08.530

This should work.

;with cte as
(
    SELECT Min(Inquirer.ID) as MinID, Max(Inquirer.ID) as MaxID, 
    Min(Inquirer.Program) as MinProg, Max(Inquirer.Program) as MaxProg,
    COUNT(Inquirer.Name) AS NumOccurrences
    FROM Inquirer
    GROUP BY Inquirer.Name
    HAVING ( COUNT(*) = 2 )
)
    Insert into Transfer
    (InquiryID_fk, ReferElsewhere, ReferMcLean, ReferExternal, ReferredFrom, TransferInquiryID_fk)
    SELECT imin.id,'Yes',MaxProg,NULL,MinProg,imax.ID 
    from cte
        inner join inquiry imin on MinID = imin.inquirer_Id 
        inner join inquiry imax on MaxID = imax.inquirer_Id

but what I think you want is for the select to read...

    SELECT imin.id,'Yes',qmax.Program,NULL,qmin.Program,imax.ID 
    from cte
        inner join inquiry imin on MinID = imin.inquirer_Id 
                 inner join inquirer qmin on minid = qmin.id
        inner join inquiry imax on MaxID = imax.inquirer_Id 
                 inner join inquirer qmax on minid = qmax.id

... but it depends on which variety of SQL you have

Thank you very much for your suggestion, this works just as i needed it to. One thing that is confusing me however, is that it seems half of the resultset gives me the MinProg and MaxProg backwards, basically the MinProg should be the Program associated with MinID, and MaxProg associated with MaxID, but when it displays the MinProg is the program associated with MaxID and vice versa, any idea why this might be? Again, thank you for your help on my initial question — Nick G, Aug 02 '12 at 14:11
That's based on your original query using Min (Inquirer.Program) as MinProg - which will not return the Program with the lowest ID, but the first alphabetically. Let me edit the above... — podiluska, Aug 02 '12 at 14:14

Using SQL result variables in Nested SELECT statement

4 Answers4

Linked