Problem to write a query for specific result

Question

Hello Guys I want take your help to solve out my question so please give me better way to implement the query to get records from search parameter Below I have drawn one scenario and from that I want to get result Suppose one organization held the draw of lottery ticket of various categories LotteryTicketBuyerMaster BuyerId(Int) BuyerName(varchar) EmailId Category(varchar) TicketNo(Varchar) 1 Nicks Nicks@email.com Electronics 1100111007 2 Nicks Nicks@email.com Automobiles 1401514020 3 James james@email.com Electronics 1100211005 4 James james@email.com Automobiles 1401614017 5 Mike mike@email.com Electronics 11003 6 Nelson nelson@email.com Electronics 1100411006 7 Mira mira@email.com Furniture 13001 8 Jack jack@email.com Automobiles 1401814019 9 Mark mark@email.com Furniture 1300213004 10 Lary lary@email.com Furniture 13003 LuckyTicketMaster 1 st Day Draw DrawDate(Date) Category(Varchar) LuckyTicketNo(Varchar) 01/03/2017 Electronics 1100111003 01/03/2017 Automobiles 1401614018 2 nd Day Draw DrawDate(Date) Category(Varchar) LuckyTicketNo(Varchar) 02/03/2017 Electronics 1100511006 02/03/2017 Automobiles 1401814020 02/03/2017 Furniture 1300213003 And at end of the each day I want to send email to lucky winner so how to write a query in better way to get winner Name and Email in LotteryTicketBuyerMaster from TicketNo by LuckyTicketMaster’s LuckyTicketNo ResultTable 1 st Day ResultTable Date BuyerName Emailid Category TicketNo 01/03/2017 Nicks nicks@email.com Electronics 11001 01/03/2017 Mike mike@email.com Electronics 11003 01/03/2017 James james@email.com Automobiles 14016 01/03/2017 Jack jack@email.com Automobiles 14018

Nitin Sontakke · Answer

If you have slightly earlier version of SQL Server then use following script to create a function: create function [dbo].[Split] ( @string nvarchar( max ) @delimiter char (1) ) returns @ output table ( [id] int [splitdata] nvarchar( max ) ) begin /* select * from [dbo].[Split](N '1233451245615785'  N '' ) */ declare @start integer = 1 @ end integer = charindex(@delimiter @string) @i integer = 0; while @start begin if @ end = 0 set @ end = LEN(@string) + 1 if ( substring (@string @start @ end - @start) '' ) begin set @i = @i + 1 insert into @ output ([splitdata] [id]) values ( substring (@string @start @ end - @start) @i) end set @start = @ end + 1 set @ end = charindex(@delimiter @string @start) end return end And then use following query: select l.DrawDate t.BuyerName t.EmailId t.Category t.[splitdata] [TicketNo] from @LuckyTicketMaster l cross apply dbo.Split(l.LuckyTicketNo '' ) k left outer join ( select * from @LotteryTicketBuyerMaster t cross apply dbo.Split(t.TicketNo '' ) ) t on k.[splitdata] = t.[splitdata] where [DrawDate] = '20170301'

Nitin Sontakke · Answer

@Manish strictly speaking it wasn't even a suggestion. 'Ground work' is probably the proper word. The full background is actually this: I wanted to work on solution but had no base data which I had to prepare from html tables pasted in question. So I prepared the script for that. By the time i was about to start the query it was time to leave for the home. So I thought might as well paste the script here itself so that I can pick it up at home. In addition if someone else is working on it s/he doesn't have to take efforts to do something which I already did. What I particularly disliked is someone down-voted my reply. Anyways...this is probably common in public forums. Better leave it to that. Thanks!

Manish Kumar · Answer

@Nitin.. Instead of answer it should be suggestion. My Mistake

Nitin Sontakke · Answer

.and here is ANSWER using SQL Server 2016. declare @LotteryTicketBuyerMaster table ( BuyerId Integer BuyerName varchar (50) EmailId varchar (50) Category varchar (50) TicketNo varchar (50) ) insert into @LotteryTicketBuyerMaster values (1 'Nicks'  'Nicks@email.com'  'Electronics'  '1100111007' ) (2 'Nicks'  'Nicks@email.com'  'Automobiles'  '1401514020' ) (3 'James'  'james@email.com'  'Electronics'  '1100211005' ) (4 'James'  'james@email.com'  'Automobiles'  '1401614017' ) (5 'Mike'  'mike@email.com'  'Electronics'  '11003' ) (6 'Nelson'  'nelson@email.com'  'Electronics'  '1100411006' ) (7 'Mira'  'mira@email.com'  'Furniture'  '13001' ) (8 'Jack'  'jack@email.com'  'Automobiles'  '1401814019' ) (9 'Mark'  'mark@email.com'  'Furniture'  '1300213004' ) (10 'Lary'  'lary@email.com'  'Furniture'  '13003' ) declare @LuckyTicketMaster table ( DrawDate Date Category Varchar (50) LuckyTicketNo Varchar (50) ) insert into @LuckyTicketMaster values ( '2017/03/01'  'Electronics'  '1100111003' ) ( '2017/03/01'  'Automobiles'  '1401614018' ) ( '2017/03/02'  'Electronics'  '1100511006' ) ( '2017/03/02'  'Automobiles'  '1401814020' ) ( '2017/03/02'  'Furniture'  '1300213003' ) select l.DrawDate t.BuyerName t.EmailId t.Category t.value [TicketNo] from @LuckyTicketMaster l cross apply string_split(l.LuckyTicketNo '' ) k left outer join ( select * from @LotteryTicketBuyerMaster t cross apply string_split(t.TicketNo '' ) ) t on k.value = t.value where [DrawDate] = '20170301'

Nitin Sontakke · Answer

NOWHERE in my reply did i state that it is an answer!

Manish Kumar · Answer

Apart from Nitin answer i guess you need to create a UDF (table value) for splitting ticketNo. from comma into a row.

Nitin Sontakke · Answer

Something to begin with whoever is attempting to solve! declare @LotteryTicketBuyerMaster table ( BuyerId Integer BuyerName varchar (50) EmailId varchar (50) Category varchar (50) TicketNo varchar (50) ) insert into @LotteryTicketBuyerMaster values (1 'Nicks'  'Nicks@email.com'  'Electronics'  '1100111007' ) (2 'Nicks'  'Nicks@email.com'  'Automobiles'  '1401514020' ) (3 'James'  'james@email.com'  'Electronics'  '1100211005' ) (4 'James'  'james@email.com'  'Automobiles'  '1401614017' ) (5 'Mike'  'mike@email.com'  'Electronics'  '11003' ) (6 'Nelson'  'nelson@email.com'  'Electronics'  '1100411006' ) (7 'Mira'  'mira@email.com'  'Furniture'  '13001' ) (8 'Jack'  'jack@email.com'  'Automobiles'  '1401814019' ) (9 'Mark'  'mark@email.com'  'Furniture'  '1300213004' ) (10 'Lary'  'lary@email.com'  'Furniture'  '13003' ) declare @LuckyTicketMaster table ( DrawDate Date Category Varchar (50) LuckyTicketNo Varchar (50) ) insert into @LuckyTicketMaster values ( '2017/03/01'  'Electronics'  '1100111003' ) ( '2017/03/01'  'Automobiles'  '1401614018' ) ( '2017/03/02'  'Electronics'  '1100511006' ) ( '2017/03/02'  'Automobiles'  '1401814020' ) ( '2017/03/02'  'Furniture'  '1300213003' )

Nitin Sontakke · Answer

Which version of SQL Server are you using?

BuyerId(Int)	BuyerName(varchar)	EmailId	Category(varchar)	TicketNo(Varchar)
1	Nicks	[email protected]	Electronics	11001,11007
2	Nicks	[email protected]	Automobiles	14015,14020
3	James	[email protected]	Electronics	11002,11005
4	James	[email protected]	Automobiles	14016,14017
5	Mike	[email protected]	Electronics	11003
6	Nelson	[email protected]	Electronics	11004,11006
7	Mira	[email protected]	Furniture	13001
8	Jack	[email protected]	Automobiles	14018,14019
9	Mark	[email protected]	Furniture	13002,13004
10	Lary	[email protected]	Furniture	13003

DrawDate(Date)	Category(Varchar)	LuckyTicketNo(Varchar)
01/03/2017	Electronics	11001,11003
01/03/2017	Automobiles	14016,14018

Date	BuyerName	Emailid	Category	TicketNo
01/03/2017	Nicks	[email protected]	Electronics	11001
01/03/2017	Mike	[email protected]	Electronics	11003
01/03/2017	James	[email protected]	Automobiles	14016
01/03/2017	Jack	[email protected]	Automobiles	14018