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

General SQL Server Forums

New to SQL Server Programming

Query help

Author

Topic

vree
Starting Member

30 Posts

Posted - 2005-09-27 : 14:14:47

I have an SQL Server db; for meeting registrations online; I have tables that store the regMeeting name, regPrice, regEntity, regContacts, regEntPayment

I want to retrieve each each item plus price that a person registered for. I have this, which is not working:

SELECT DISTINCT
dbo.regContacts.contFName, dbo.regContacts.contLName,
dbo.regEntity.entId, dbo.regPrice.priceLbl,
dbo.regPrice.earlyPrice, dbo.regPrice.regPrice
FROM dbo.regContacts INNER JOIN
dbo.regEntity ON
dbo.regContacts.entId = dbo.regEntity.entId INNER JOIN
dbo.regAttendees regAttendees1 ON
dbo.regContacts.contId = regAttendees1.contId INNER JOIN
dbo.regMeetings ON
regAttendees1.meetId = dbo.regMeetings.meetId INNER JOIN
dbo.regPrice ON
dbo.regMeetings.meetId = dbo.regPrice.meetId RIGHT OUTER JOIN
dbo.regAttendees ON
dbo.regMeetings.meetId = regAttendees1.meetId AND
dbo.regContacts.contId = regAttendees1.contId
WHERE (regAttendees1.meetId = '13')

it returns one of each price in the price table instead of one price for each person registered. I hope someone can decisfer this, I will post info to any clarifying questions. Its making me crazy because I dont know how to do this.

SamC
White Water Yakist

3467 Posts

Posted - 2005-09-27 : 15:20:45

I'd like to see the table Structures.

SELECT 
RC.contFName, RC.contLName, RC.entId, RC.priceLbl, RC.earlyPrice, RCe.regPrice
  FROM dbo.regContacts RC
  INNER JOIN dbo.regEntity RE
    ON  RC.entId = RE.entId 
  INNER JOIN dbo.regAttendees RA1
    ON  RC.contId = RA1.contId 
  INNER JOIN dbo.regMeetings RM
    ON  RA1.meetId = RM.meetId 
  INNER JOIN dbo.regPrice RP
    ON  RM.meetId = RP.meetId 

  WHERE RA1.meetId = '13'

There is no information on the source data. I *assumed* that there's only one price per person, but your post sounds like this may not be the case.

If there's more than 1 price (or row) per person, it may be necessary to GROUP BY to get the desired recordset results.