Select n records in table X from a field on table Y

  • luisalfonso70

    Old Hand

    Points: 300

    Hi, hope someone can help with this.

    I've the table gifts_to_give:

    Id_give      number_of_gifts          name                  city

    1                     5                             luis               Barcelona

    2                     3                            pedro           Albacete

    3                    10                           Antonio       Madrid


    and This is the other table "gifts” which contains like 100 records of each city with different gifts :

    gift_id           city           description          Price

    1                Barcelona       Shoes                    1500

    2               Barcelona       Ring                         200

    3               Barcelona       Pen                           50


    What I wish to accomplish is according to the fields “city” and  “number_of_gifts”  on the  table "gifts_to_give" select X random number of records of the table "gifts" matching the "number of gifts" field and city


    The fields that I need on the query results are:

    Id_give, number_of_gifts, name, city, gift_id, description, price.




  • Site Owners

    SSC Guru

    Points: 80378

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • mohan.muralid

    SSC Journeyman

    Points: 99

    I have created 2 tables asper your requirements and inserted the data into both tables


    Id_give number_of_gifts name city

    1 5 luis Barcelona

    2 3 Pedro Albacete

    3 10 Antonio Madrid

    Gifts:- having

    gift_Id city description price

    1 Barcelona Shoes 1500

    2 Barcelona Ring 200

    3 Barcelona Pen 50

    4 Barcelona BackBag 350

    5 Albacete Shoes 1200

    6 Albacete Laptop 1100

    7 Madrid Kit 200

    8 Madrid Dress 300

    9 Albacete Mobile 299

    10 Barcelona Box 200

    11 Madrid Pen 20

    12 Madrid Shoes 800

    13 Madrid Bike 3000

    14 Madrid Mobile 399

    15 Madrid Utilities 599

    16 Madrid Personal Things 499

    The below query will returns the  your requested result:-

    select Id_give, number_of_gifts, name,, gift_id, description, price

    from gifts g

    join gifts_to_give gg  on = and

    (select count(  from gifts g1 where = group by = gg.number_of_gifts

    order  by 1



    Id_give number_of_gifts name city gift_id description price

    1 5 luis Barcelona 1 Shoes 1500

    1 5 luis Barcelona 2 Ring 200

    1 5 luis Barcelona 3 Pen 50

    1 5 luis Barcelona 4 BackBag 350

    1 5 luis Barcelona 10 Box 200

    2 3 Pedro Albacete 5 Shoes 1200

    2 3 Pedro Albacete 6 Laptop 1100

    2 3 Pedro Albacete 9 Mobile 299


    I hope this is the output you are expecting.

  • Jeff Moden

    SSC Guru

    Points: 995643

    Ok... both of you need to read'n'heed the article at the link in my signature line below if you want help.  Posting data as text doesn't help or are answering a question.  The data needs to be "readily consumable" data to get both the quickest and the best help with your questions.  That also includes answers to a problem so that others might be able to 1) verify the solution works and 2) maybe come up with a more efficient answer.

    Also learn how to use the {;} Insert/edit code sample button in the icon bar when you post, please.  Thanks.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jonathan AC Roberts


    Points: 17159

    IF OBJECT_ID('tempdb..#gifts_to_give','U') IS NOT NULL 
    DROP TABLE #gifts_to_give;

    SELECT *
    INTO #gifts_to_give
    (1, 5, 'luis', 'Barcelona'),
    (2, 3, 'pedro', 'Albacete'),
    (3, 10, 'Antonio', 'Madrid')
    ) T(Id_give, number_of_gifts, name, city)
    IF OBJECT_ID('tempdb..#gifts','U') IS NOT NULL
    DROP TABLE #gifts;
    SELECT *
    INTO #gifts
    (1, 'Barcelona', 'Shoes', 1500),
    (2, 'Barcelona', 'Ring', 200),
    (3, 'Barcelona', 'Pen', 50)
    ) T(gift_id, city, description, Price)

    SELECT *
    FROM #gifts_to_give gtg
    CROSS APPLY(SELECT TOP(gtg.number_of_gifts) *
    FROM #gifts g
    WHERE =

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply