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

  • 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.









  • pietlinden

    SSC Guru

    Points: 62672

    There's not enough data to test your query, but this is the general pattern and should work:

    use tempdb;
    CREATE TABLE #ToGive (
     GiverID INT,
     NumGifts TINYINT,
     GiverName VARCHAR(10),
     City VARCHAR(15)
    CREATE TABLE #Gifts (
     gift_id int,
     city varchar(20),Giftdescription varchar(20), price int);

    It's just a simple cross apply. Kenneth Fisher has a really nice article about it on his website.  In this example, I'm using it to run the CROSS APPLY'd query once for every record in the outer query.

    SELECT t.GiverID
     , t.GiverName
     , t.City
     , gg.Giftdescription
     , gg.Price
    FROM #ToGive t
    CROSS APPLY (SELECT TOP (t.NumGifts) city, Giftdescription, price
        FROM #Gifts g WHERE t.City = g.City) gg;

    I modified your data so that each person gives 1, 2 0r 3 gifts, and I left out the City part (only because you don't have enough data in your example.

    SELECT t.GiverID
     , t.GiverName
     , t.City
     , t.NumGifts
     , gg.Giftdescription
     , gg.Price
    FROM #ToGive t
    CROSS APPLY (SELECT TOP (t.NumGifts) city, Giftdescription, price
        FROM #Gifts g /*WHERE t.City = g.City*/) gg
    ORDER BY t.GiverName, t.City;

    but that should be enough to show you the pattern.

    And while I'm at it... please read this - to help us help you better.

    Forum Etiquette: How to post data/code on a forum to get the best help

    • This reply was modified 7 months ago by  pietlinden.
  • This was removed by the editor as SPAM

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

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