TSQL Challenge

On the series on SQL challenges, below is the next puzzle/challenge and the requirement is to find out the person name who has both mango and apple

Table 1: @Person contains Person Name
Table 2: @PersonFruits contains the available fruits per each person

DECLARE @Person table (UserId INT, Name VARCHAR(25))

INSERT INTO @Person
SELECT 1, 'John Doe' union all
SELECT 2, 'Jane Doe' union all 
SELECT 3, 'Joe Blogs'

DECLARE @PersonFruit TABLE (KeywordID INT, UserID INT, Keyword NVARCHAR(35))

INSERT INTO @PersonFruit
SELECT 1,  1, 'Apple' union all     
SELECT 2,  1, 'Orange'  union all   
SELECT 3,  2, 'Mango' union all
SELECT 4,  2, 'Apple' union all
SELECT 5,  3, 'Mango' union all  
SELECT 6,  3, 'Orange' union all
SELECT 7,  4, 'PineApple' union all
SELECT 8,  4, 'Apple'


SELECT NAME
FROM @Person
WHERE UserId IN (
		SELECT a.userid
		FROM @Person a
		INNER JOIN @PersonFruit b ON a.UserId = b.UserID
		WHERE b.Keyword IN (
				'Mango'
				,'Apple'
				)
		GROUP BY a.UserId
		HAVING count(DISTINCT keywordId) = 2
		)
This entry was posted in SQL SERVER 2008, T-SQL and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *