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.

     

     

    Thanks!!!!

  • Site Owners

    SSC Guru

    Points: 80385

    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

    Gifts_to_give:-

    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, gg.city, gift_id, description, price

    from gifts g

    join gifts_to_give gg  on

    gg.city = g.city and

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

    order  by 1

     

    OutPut:-

    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: 994266

    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."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Jonathan AC Roberts

    SSCoach

    Points: 16774

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

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

    SELECT *
    FROM #gifts_to_give gtg
    CROSS APPLY(SELECT TOP(gtg.number_of_gifts) *
    FROM #gifts g
    WHERE g.city = gtg.city
    ORDER BY NEWID()) AS g

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

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