Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Location
    London, UK
    Posts
    39

    Unanswered: Using result of one query in another

    hi,

    i've tried searching on this but not having much luck, i'm trying to use the result of one and use it in another. is there any way to do this?

    So for example, i would use the result of this query and store it in a variable called @tmp_id (if possible)

    SELECT TOP 1 ID FROM tblWines WHERE RefNo LIKE 'AB1234';

    then use that variable in another query

    INSERT INTO tblLogWines (WineID, Date) VALUES (@tmp_id, now());

    i've simplied the context to make it a little more understandable, any help is appreciated.

    goran.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Something like:
    Code:
    SERT INTO tblLogWines (
       WineID, Date
       ) SELECT TOP 1 ID, GetDate()
       FROM tblWines
       WHERE RefNo LIKE 'AB1234'
    -PatP

  3. #3
    Join Date
    Nov 2003
    Location
    London, UK
    Posts
    39
    thanks for the speedy reply but maybe i've simplified it a little too much,

    what i'm trying to do is insert the id from one table as one field among many fields (the others don't actually exist in the first table) here's an example of an actual query that i need to execute:

    INSERT INTO tblCellarPlansInfo (planID, quantity, wineColour, wineName, wineText, vintage, drinking) VALUES (10, 12, 'Red', 'Nuits-St-Georges, aux Chaignots 1er Cru, Domaine Robert Chevillon', Description goes here', '2002', '2007-2012');

    now in this example, i've hardcoded the value planID to '10' as this will work our our server, however when this is executed on the client's server, i cant guarantee that the id is going to be 10. thats why i needed to execute a query like below to find out the planID

    SELECT TOP 1 ID FROM tblCellarPlans WHERE RefNo = 'AB1234';

    thanks.
    goran.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Then I'd use:
    Code:
    SELECT TOP 1 @tmp_id = ID
       FROM tblCellarPlans
       WHERE RefNo = 'AB1234'
    -PatP

  5. #5
    Join Date
    Nov 2003
    Location
    London, UK
    Posts
    39
    it works a treat, thanks pat.

    goran

Posting Permissions

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