To show data of items not purchased

Question

Post reply

To show data of items not purchased

mathewspsimon

SSCommitted

Points: 1706
More actions
July 18, 2011 at 3:34 am

#253788

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.

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

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

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]

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]