MSSQL query help needed - 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)

MSSQL query help needed

Author

Topic

arcware
Starting Member

1 Post

Posted - 2007-10-31 : 04:21:53

Hi,
I have 2 tables, tbl_cards (id, cardname) and tbl_crdservices (cardId,serviceId).
I need to fetch data from tbl_card where the cardname should have services in tbl_cardservice. For ex. select all cards which provide serviceId 5 and 6. I can use IN keyword Like

Select c.* from tbl_cards c INNER JOIN tbl_cardServices cs ON c.Id = cs.CardId and cs.ServiceID IN (5,6)

but... IN keyword gives result on OR basis, means cards having id 5 or 6 will be returned, and I need result where the card having both services should return.

Please help.

Thanks

peteoc
Starting Member

14 Posts

Posted - 2007-10-31 : 04:25:57

My query would be

SELECT tbl_cards.id, tbl_cards.cardname, tbl_crdservices.serviceid
FROM tbl_cards, tbl_crdservices
WHERE
tbl_cards.id = tblcrdservices.cardid

this may not be the most efficient way of doing this, however I believe it would work..........give it a try :)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-31 : 04:38:15

What if CardId has more Services aprat from 5 and 6?
Do you want to return them also?

Select c.* from tbl_cards c INNER JOIN 
(
Select 
	CardId 
from 
	cardServices 
	where ServiceID in (5,6)
	group by CardId
	having count(distinct ServiceId)=2
) as cs 
ON c.Id = cs.CardId

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources