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

Question

Post reply

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

luisalfonso70

SSC-Addicted

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

#3664814

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!!!!

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

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

Site Owners SSC Guru Points: 80320 More actions · Answer 1

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 More actions · Answer 2

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: 1003863 More actions · Answer 3

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.

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 SSCoach Points: 18263 More actions · Answer 4

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