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 |
|
jpcraw
Starting Member
3 Posts |
 Posted - 2008-03-27 : 16:11:37
|
| Here is my SP. /********************************************************************************************************** Type : Procedure* Name : spGetCourseEmailList* Input Parameters : @Code1* Output Parameters : None* Purpose : This stored procedure gets the Members for a particular Chapter Event the cached items.**********************************************************************************************************/CREATE PROCEDURE [dbo].[spGetCourseEmailList]( @Code1 char(9)) AS SELECT dbo.[names].lname as LastName, dbo.[names].fname as FirstName, dbo.[evser].ses as MealChoice, dbo.[evldg].paid as AmountPaid, dbo.[names].gp as PreferredAddress, dbo.[names].mi as MiddleInitial, dbo.[names].nname as NickName, dbo.[names].xname as Suffix, dbo.[names].hphone as HomePhone, dbo.[names].email as EmailAddress, dbo.[names].addr1 as HomeAddress1, dbo.[names].addr2 as HomeAddress2, dbo.[names].city as City, dbo.[names].st as State, dbo.[names].zip as ZipCode, dbo.[firms].fname1 as FirmName1, dbo.[firms].fname2 as FirmName2, dbo.[firms].faddr1 as FirmAddress1, dbo.[firms].faddr2 as FirmAddress2, dbo.[firms].fcity as FirmCity, dbo.[firms].fst as FirmState, dbo.[firms].fzip as FirmZip, dbo.[firms].fphone as FirmPhone, dbo.[names].udflist1 FROM dbo.[firms] INNER JOIN dbo.[names] ON dbo.[firms].firm = dbo.[names].firmINNER JOIN dbo.evldgON dbo.[names].id = dbo.[evldg].idINNER JOIN dbo.evserON dbo.[evldg].id = dbo.[evser].idWHERE dbo.[evldg].code1 = @Code1 AND dbo.[evser].code1 = @Code1 AND dbo.[names].xwebflag <> 'Y'ORDER BY dbo.[names].lname, dbo.[names].fnameGOWe run a report for our members of people that have signed up for a class. What I need to do is add another table evreg but I don't want to duplicate names which are already being pulled from evser in the above code. How would I add this? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-03-27 : 16:23:42
|
| It's best to show us a data example since we aren't familiar with your schema. What do you mean by duplicate names?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
jpcraw
Starting Member
3 Posts |
Posted - 2008-03-27 : 16:27:24
|
| When someone signs up for a meeting and selects a session for that meeting, their info is stored in the table evser, but if they do not choose a session they info is only stored here: evreg. So they could be in both tables or only in evreg. What is happening when the report is pulled it's not pulling names from evreg only evser. I want to add evreg to the SP, but not duplicate their names if they are in both tables. Does that make sense? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-03-27 : 16:30:59
|
| Use an OUTER JOIN to evreg.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
jpcraw
Starting Member
3 Posts |
Posted - 2008-03-27 : 16:36:08
|
| So like this?OUTER JOIN dbo.evserON dbo.[evreg].id = dbo.[evser].idDo I need to add anything to the where statement?Thanks so much for your help |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-03-27 : 16:46:01
|
| First, it should be to evreg since evser is already in your joins. Specify either LEFT JOIN or LEFT OUTER JOIN. See SQL Server Books Online for join details and syntax.Second, try the updated code and let us know what issues you see.Third, it's hard to know without showing us a data example as mentioned in my first reply. Show us sample rows of your current code and then what it should look like after the change has been made.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
|
|
Stored Procedure