Help in making SQl Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help in making SQl Query

Author

Topic

sanjay2988
Starting Member

16 Posts

Posted - 2008-12-19 : 00:19:09

i want to search for a pattern accross rows in a table. for eg :--
i have POS data in my table.
ticketID siteID ActionCode strDescription
9 2 Item Sale Large Pepsi
9 2 Item Sale Small Pepsi
9 2 DD Discount
10 2 DD Discount
10 2 CC Credit
10 2 Item Sale Fries
10 2 Item Sale Large Pepsi
etc etc..
Now iwant to search for ActionCode And strDesciption within a ticket.
for eg:-- i want to search for all tickets having fries and discount as transaction. so this should show me only ticketID 10 as 9 doesnt have fries in it... Help me out as soon as possible....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-19 : 00:48:02

declare @search table (data varchar(20))
insert @search select '%fries%' union all select '%discount%'

SELECT t1.TicketID
FROM Table1 as t1
inner join @search AS s on t1.strDescription LIKE s.data
GROUP BY TicketID
HAVING t1.ticketid = (SELECT COUNT(*) from @search)

E 12°55'05.63"
N 56°04'39.26"

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-19 : 03:54:00

SELECT ticketid FROM tbl WHERE strdescription IN('discount','fries') GROUP BY ticketid HAVING COUNT(strdescription) = 2

Jai Krishna

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-19 : 03:58:47

Jai, that will return any ID that have duplicate 'discount' or duplicate 'fries' values.
Add this sample data twice and run your query again

9 2 DD Discount
9 2 DD Discount

E 12°55'05.63"
N 56°04'39.26"

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-19 : 04:03:34

Try This

SELECT ticketid FROM tbl WHERE strdescription IN('discount','fries') GROUP BY ticketid HAVING COUNT(DISTINCT strdescription) = 2

Jai Krishna

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-19 : 04:35:07

quote:
Originally posted by Peso

declare @search table (data varchar(20))
insert @search select '%fries%' union all select '%discount%'

SELECT t1.TicketID
FROM Table1 as t1
inner join @search AS s on t1.strDescription LIKE s.data
GROUP BY TicketID
HAVING t1.ticketid = (SELECT COUNT(*) from @search)

E 12°55'05.63"
N 56°04'39.26"

Hi peso,
ur comparing count with ticketid in having clause once check it out

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-19 : 04:44:29

declare @search table (data varchar(20))
insert @search select '%fries%' union all select '%discount%'

SELECT t1.ticketid
FROM tb11 as t1
inner join @search AS s on t1.strDescription LIKE s.data
GROUP BY TicketID
HAVING COUNT(DISTINCT t.strdescription)=2

Jai Krishna

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-19 : 04:50:00

True.
I blame Copy and Paste!

E 12°55'05.63"
N 56°04'39.26"

sanjay2988
Starting Member

16 Posts

Posted - 2008-12-19 : 08:28:26

Hello All,
But i have strDescription as Large pepsi, Small pepsi, Medium Pepsi.. so %pepsi% will return all rows containing these 3 values. Then how will we distinguish between them because this condition will went wrong if this is the case...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-19 : 08:41:45

[code]declare @search table (data varchar(20))
insert @search select '%fries%' union all select '%discount%'

SELECT t1.ticketid
FROM tb11 as t1
inner join @search AS s on t1.strDescription LIKE s.data
GROUP BY TicketID
HAVING COUNT(DISTINCT s.data) = (SELECT COUNT(*) from @search)[/code]

E 12°55'05.63"
N 56°04'39.26"

sanjay2988
Starting Member

16 Posts

Posted - 2008-12-19 : 08:55:21

Hi Peso,
can i have ur email id.. this is not solving my purpose.. as querying pepsi returns me suppose 2 rows and there is no discount in ticket 9. then also this condition will satisfy. it will be shown in the result because rows will be 2 i.e small pepsi and large pepsi without discount..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-19 : 09:02:59

Works ok for me... Is there something you are not telling us?

DECLARE	@Sample TABLE
	(
		ticketID INT,
		siteID INT,
		ActionCode VARCHAR(20),
		strDescription VARCHAR(100)
	)
	
INSERT	@Sample
SELECT	 9, 2, 'Item', 'Sale Large Pepsi' UNION ALL
SELECT	 9, 2, 'Item', 'Sale Small Pepsi' UNION ALL
SELECT	 9, 2, 'DD', 'Discount' UNION ALL
SELECT	10, 2, 'DD', 'Discount' UNION ALL
SELECT	10, 2, 'CC', 'Credit' UNION ALL
SELECT	10, 2, 'Item', 'Sale Fries' UNION ALL
SELECT	10, 2, 'Item', 'Sale Large Pepsi'

DECLARE	@Search TABLE
	(
		data VARCHAR(20)
	)

INSERT	@Search
SELECT	'%fries%' UNION ALL
SELECT	'%discount%'

SELECT		x.ticketid
FROM		@Sample AS x
INNER JOIN	@Search AS s ON x.strDescription LIKE s.data
GROUP BY	x.TicketID
HAVING		COUNT(DISTINCT s.data) = (SELECT COUNT(*) FROM @Search)

E 12°55'05.63"
N 56°04'39.26"

sanjay2988
Starting Member

16 Posts

Posted - 2008-12-21 : 01:38:56

Thanks this will work

sanjay2988
Starting Member

16 Posts

Posted - 2008-12-21 : 01:48:22

one more help i want is we can search on the basis of actioncode alos keeping strdescription blank. like i want to search all CC transactions and in Item only those having strdescription as pepsi.. What to do for this

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources