get multiple records with the where clausulle

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 2008 Forums

Transact-SQL (2008)

get multiple records with the where clausulle

Author

Topic

DennisL.
Starting Member

4 Posts

Posted - 2012-03-05 : 05:52:21

Hello,

My first post here and I hope you can help me.
I am trying to get multiple records out of a database.

SELECT     Contact.fullname, Opportunity.crme_evenementidname, Contact.crme_opleiding
FROM         Contact INNER JOIN
                      Opportunity ON Contact.contactid = Opportunity.contactid
                      where Opportunity.crme_evenementidname = 'MBOXperience 2010'

This query gives me only the records with MBOXperience 2010. I know that some contacts (those with the MBOXperience filter) have multiple opportunity records. My question is how can I get these records out of the database.

Thanks already for your help

noamg
Posting Yak Master

215 Posts

Posted - 2012-03-05 : 06:00:53

hi,
I don't understand your question ...
please explain yourself

Noam Graizer

DennisL.
Starting Member

4 Posts

Posted - 2012-03-05 : 06:16:35

In our database a contact/person can have multiple opportunity records. This means when I search for a persons in the opportunity table, the result could be that the person wil l return more than 1 time. In the opportunity table there are multiple events one of these events is the MBOXperience 2010.
What I would like to see are all the persons that have been to the event MBOXperience 2010 (this is not the problem)with all there other related opportunity records.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-03-05 : 07:05:50

Try this:

SELECT     C.fullname, O.crme_evenementidname, C.crme_opleiding
FROM         Contact C INNER JOIN
                      Opportunity O ON C.contactid = O.contactid
where exists(select * from Opportunity O2 
             where O2.crme_evenementidname = 'MBOXperience 2010'
             and O2.contactid = O.contactid)

No, you're never too old to Yak'n'Roll if you're too young to die.

DennisL.
Starting Member

4 Posts

Posted - 2012-03-05 : 10:56:20

Thanks! Never knew I could use exists. Perhaps to obvious :) Thanks again you saved me a lot of work :)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-03-05 : 10:58:24

welcome