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

Question

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

luisalfonso70

SSC-Addicted

Points: 410
More actions
July 21, 2019 at 2:03 am

#3664812

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!!!!
July 21, 2019 at 4:34 pm

This was removed by the editor as SPAM

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

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

pietlinden SSC Guru Points: 63509 More actions · Answer 1

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

use tempdb;
GO
CREATE TABLE #ToGive (
 GiverID INT,
 NumGifts TINYINT,
 GiverName VARCHAR(10),
 City VARCHAR(15)
 );
GO
INSERT INTO #ToGive VALUES 
(1,5,'luis','Barcelona'),
(2,3,'pedro','Albacete'),
(3,10,'Antonio','Madrid');
CREATE TABLE #Gifts (
 gift_id int,
 city varchar(20),Giftdescription varchar(20), price int);
GO

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. https://sqlstudies.com/2013/05/20/the-many-uses-of-cross-apply/

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 5 years, 5 months ago by pietlinden.