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

General SQL Server Forums

New to SQL Server Programming

SQL query

Author

Topic

yoga.redhat
Starting Member

1 Post

Posted - 2014-07-28 : 14:37:57

Child table
I'd. Frame. Lame
1. Ymc. M
2. xyz. B
3. Abc. V

Treat. Table

Treated. Childid. Treat type
6001. 1. Toffee
6002. 2. Toffee
6004. 3. Toffee
6007. 3. Ice cream

How to get a fname and name of person who have both the treat type( toffee and ice cream)

Can any one help me get query for that

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-28 : 15:14:52

There are lost of ways to do this, but this is pretty quick and straight forward

CREATE TABLE #Childtable
(
ID Int,
Fname Varchar(20),
Lname Varchar(20)
)
INSERT INTO #Childtable
VALUES(1,'Ymc','M'),(2,'xyz','v'),(3,'Abc','b')

CREATE TABLE #TreatTable
(Treated int, Childid int, Treattype varchar(20))

INSERT INTO #TreatTable
VALUES(6001,1,'Toffee'),(6002,2,'Toffee'),(6003,3,'Toffee'),(6007,1,'Ice Cream')

SELECT * FROM #TreatTable
SELECT * FROM #Childtable

SELECT FNAME
FROM #Childtable CT
WHERE
EXISTS
( SELECT 1 FROM #TreatTable TT
WHERE CT.ID = TT.ChildID
AND Treattype = 'Toffee'
)AND
EXISTS
( SELECT 1 FROM #TreatTable TTT
WHERE CT.ID = TTT.ChildID
AND Treattype = 'Ice Cream'
)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources