select query help - 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)

select query help

Author

Topic

iforum
Starting Member

1 Post

Posted - 2009-06-20 : 07:40:42

hi,
i need sql query to get list of all home which is having equipment 'Mixture grinder' as well as 'Washing machine'.
My tables will be like this.
Table - Home
HomeNumber INT,
HomeAddress VARCHAR(256),
HomeEquipmentID INT

Table - HomeEquipment
HomeEquipmentID INT,
EquipmentName VARCHAR(64)

INSERT INTO HomeEquipment (HomeEqupmentID,EquipmentName) VALUES (1,'Mixture Grinder')

INSERT INTO HomeEquipment (HomeEqupmentID,EquipmentName) VALUES (2,'Washing Machine')

INSERT INTO HomeEquipment (HomeEqupmentID,EquipmentName) VALUES (3,'Telephone')

INSERT INTO Home (HomeNumber, HomeAddress, HomeEquipmentID) VALUES(1000,'1st Cross, NewJersey',1)

INSERT INTO Home (HomeNumber, HomeAddress, HomeEquipmentID) VALUES(1000,'1st Cross, NewJersey',1)

INSERT INTO Home (HomeNumber, HomeAddress, HomeEquipmentID) VALUES(1000,'1st Cross, NewJersey',2)

INSERT INTO Home (HomeNumber, HomeAddress, HomeEquipmentID) VALUES(1001,'1st Cross, Paris',3)

INSERT INTO Home (HomeNumber, HomeAddress, HomeEquipmentID) VALUES(1002,'1st Cross, paris',2)

INSERT INTO Home (HomeNumber, HomeAddress, HomeEquipmentID) VALUES(1003,'1st Cross, London',1)

I need a sql query which gets me all the homes which contains both 'Mixture Grinder' and 'Washing Machine'

Please help me with a select statement.

Raghu Sunkara

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-20 : 07:51:11

select homenumber,homeaddress,h.homeequipmentid,h.equipmentname
from home h
inner join homeequipment he on he.homeequipmentid = h.homeequipmentid
where h.homeequipmentid in (1,2)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources