July 18, 2011 at 3:34 am
Hi Gurus, below is the data
CREATE TABLE [dbo].[TT](
[Cust] [int] NULL,
[Item] [int] NULL)
GO
INSERT INTO [OASISDEV].[dbo].[TT]
([Cust]
,[Item])
VALUES (122433,2)
GO
INSERT INTO [OASISDEV].[dbo].[TT]
([Cust]
,[Item])
VALUES
(143784,15)
GO
INSERT INTO [OASISDEV].[dbo].[TT]
([Cust]
,[Item])
VALUES
(143784,23)
GO
INSERT INTO [OASISDEV].[dbo].[TT]
([Cust]
,[Item])
VALUES
(143784,25)
GO
INSERT INTO [OASISDEV].[dbo].[TT]
([Cust]
,[Item])
VALUES
(143784,27)
GO
INSERT INTO [OASISDEV].[dbo].[TT]
([Cust]
,[Item])
VALUES
(77342,15)
GO
INSERT INTO [OASISDEV].[dbo].[TT]
([Cust]
,[Item])
VALUES
(77342,39)
GO
INSERT INTO [OASISDEV].[dbo].[TT]
([Cust]
,[Item])
VALUES
(77342,83)
GO
INSERT INTO [OASISDEV].[dbo].[TT]
([Cust]
,[Item])
VALUES
(12773,5)
GO
INSERT INTO [OASISDEV].[dbo].[TT]
([Cust]
,[Item])
VALUES
(124820,8)
GO
INSERT INTO [OASISDEV].[dbo].[TT]
([Cust]
,[Item])
VALUES
(161152,21)
GO
SELECT [Cust]
,[Item]
FROM [OASISDEV].[dbo].[TT] ORDER BY [Cust]
In this i want to fetch distinct customers who has not brought the product code 15.that is Customer 77342 & 143784 has brought this product so they should not be in the list.
Can some one help me with this please.Thanks in advance.
July 18, 2011 at 3:47 am
Untested...
SELECT [Cust]
,[Item]
FROM [OASISDEV].[dbo].[TT] TT1
WHERE NOT EXISTS (SELECT 1 FROM [OASISDEV].[dbo].[TT] TT2 WHERE
TT1.[Cust]=TT2.[Cust] AND TT2.[Item] = 15 )
GROUP BY [Cust]
ORDER BY [Cust]
July 18, 2011 at 3:48 am
SELECT DISTINCT a.Cust
FROM dbo.TT a
WHERE NOT EXISTS (SELECT * FROM dbo.TT b WHERE b.Cust=a.Cust AND b.Item=15)
ORDER BY a.Cust;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537July 18, 2011 at 3:51 am
thanks to all
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply