one to many selection - 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 2000 Forums

Transact-SQL (2000)

one to many selection

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-03-17 : 08:29:42

Prasad writes "I have two tables like this

Table person

Id name city
1 person1 nyc
2 person2 bos
3 person3 hyd

Table gadgets

owner gadget

1 cell
1 camera
1 pda
1 gadget4
2 radio
2 cell
2 ipod
3 cell
3 gadget3
3 gadget5

If I want to run a query to select all names where city = nyc and has gadgets
such as cell and camera, gad4, gad5, etc. how do I write query for this in mysql.

Select * from person join gadgets on person.id=gadgets.owner where ...

The number of gadgets I select are given at runtime so they could be any number.

Thanks,
Prasad."

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-03-17 : 09:20:29

Not sure how you'd do it in MySQL, this is a SQL Server forum. In T-SQL your statement may be something like

SELECT * FROM person WHERE ID IN (SELECT id FROM gadgets WHERE gadget IN ('cell', 'camera'))

Although if the gadgets are given at runtime the 'WHERE gadget IN ..' clause would have to be built dynamically and executed using sp_ExecuteSQL.

Raymond

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources