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)

SQL Query

Author

Topic

sanjay2988
Starting Member

16 Posts

Posted - 2008-12-24 : 05:18:51

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)
ActionCode VARCHAR(20)
)

INSERT @Search
SELECT 'Item','%fries%' UNION ALL
SELECT 'DD','%discount%'

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

in above query if user doesnot pass strDescription to search if he passes only Actioncode then it fails.. suppose i want to search all ticketID having Item as actioncode and DD as other then having condition fails.... Kindly suggest.

raky
Aged Yak Warrior

767 Posts

Posted - 2008-12-24 : 05:30:04

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

try it once

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources