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.
| Author |
Topic |
|
vree
Starting Member
30 Posts |
 Posted - 2006-08-08 : 16:01:43
|
| I have an SQL Server DB with this query statement using SQL Express:SELECT dbo.tblContact.FirstName + ' ' + dbo.tblContact.LastName AS COMMITTEENAME, dbo.tblCommittee.Committee, dbo.tblMemberCompany.CompanyName, dbo.tblContact.Address1, dbo.tblContact.City, dbo.tblContact.State, dbo.tblContact.Zip, dbo.tblContact.Phone, dbo.tblContact.Fax, dbo.tblContact.EmailFROM dbo.tblCommitteeMember INNER JOIN dbo.tblMemberContact ON dbo.tblCommitteeMember.MemberContID = dbo.tblMemberContact.MemberContId INNER JOIN dbo.tblContact ON dbo.tblMemberContact.ContactId = dbo.tblContact.ContactID INNER JOIN dbo.tblCommittee ON dbo.tblCommitteeMember.CommitteeID = dbo.tblCommittee.CommitteeID INNER JOIN dbo.tblMemberCompany ON dbo.tblMemberContact.MemberId = dbo.tblMemberCompany.MemberID WHERE dbo.tblMemberCompany.CompanyName = 'MyCompany'ORDER BY dbo.tblMemberCompany.CompanyName, COMMITTEENAMEWhich returns this:If a person from one Company is on three committees - I get three entries; I would like to have one name, one company and all committees that they are on. Previously, I handled this in Microsoft Access at the Report Level, by linking the Reports/subReports; Is it possible to do at the query level itsself? |
|
|
vree
Starting Member
30 Posts |
Posted - 2006-08-08 : 16:29:01
|
| I am trying to explore a subquery, but am not too familiar with that however I can return all my committees for one person with this:select committee from tblcommittee where committeeid in (select committeeid from tblCommitteeMember where membercontid in (select membercontid from tblMemberContact where contactid = 697 and memberid= 31))I would like to see that with one persons name and company info?Thanks for your patience. |
 |
|
|
|
|
|
|
|
Query Question