Re: Stored Procedure Enter Same Record Twice?

From: Jeff Dillon (jeff_at_removeemergencyreporting.com)
Date: 11/19/04 

Date: Fri, 19 Nov 2004 12:18:09 -0800

No. Start with 2 tables. Obviously. 3 or 4 joins simply uses and adds to
join syntax with 2 tables.

Get simple code working first. Duh.

Jeff

"Leon" <vnality@msn.com> wrote in message
news:uc02oQmzEHA.3408@tk2msftngp13.phx.gbl...
> could you show me an example of 3 or 4 joined tables please.
>
> "David Gugick" <davidg-nospam@imceda.com> wrote in message
> news:u4zNUNmzEHA.2192@TK2MSFTNGP14.phx.gbl...
> > Leon wrote:
> >> I'm kind of new to sql server 2000. how should I go about updating my
> >> join syntax to the ANSI style. like this example..
> >> CREATE PROCEDURE GetStudentDetail
> >> @AccountID INT
> >> AS
> >> SELECT A.Username, A.Password, A.SecretQuestion, A.SecretAnswer,
> >> A.RoleID, A.Active, S.FirstName, S.LastName,
> >> S.BirthMonth, S.BirthDay, S.BirthYear, S.Gender, S.SchoolState,
> >> S.SchoolName, S.Classification, S.Major, S.EmailAddress
> >>
> >> FROM Account A INNER JOIN Student S ON A.AccountID = S.AccountID
> >> WHERE A. AccountID = @AccountID
> >> GO.
> >>
> >> "David Gugick" <davidg-nospam@imceda.com> wrote in message
> >> news:e8E5iPlzEHA.3784@TK2MSFTNGP12.phx.gbl...
> >>> Leon wrote:
> >>>> I know this stored procedure looks like am just playing around with
> >>>> redundant data, but it's necessary; however, can anyone tell me why
> >>>> the following stored procedure enter the same first winner twice,
> >>>> then enter the same second winner twice, then enter the same third
> >>>> winner twice, and on and on...Thanks!
> >>>>
> >>>> CREATE PROCEDURE AddWinner
> >>>> AS
> >>>> INSERT Winner
> >>>>
(DrawingID,TicketID,TicketCreationDate,QuickPick,AccountID,AccountCreationDa
te,EmailAddress,FirstName,
> >>>> LastName,
> >>>>
BirthMonth,BirthDay,BirthYear,Gender,SchoolState,SchoolName,Classification,M
ajor)
> >>>>
> >>>> SELECT
> >>>>
DS.CurrentDrawing,T.TicketID,T.PickDate,T.QuickPick,S.AccountID,S.Activation
Date,S.EmailAddress,S.FirstName,
> >>>> S.LastName,
> >>>>
S.BirthMonth,S.BirthDay,S.BirthYear,S.Gender,S.SchoolState,S.SchoolName,S.Cl
assification,S.Major
> >>>>
> >>>> FROM DrawSetting AS DS,Ticket AS T, Student AS S,Drawing AS D
> >>>>
> >>>> WHERE T.Active = 1 AND T.DrawingID = DS.CurrentDrawing AND
> >>>> D.DrawingID = DS.CurrentDrawing
> >>>> AND T.Num1 = D.Num1
> >>>> AND T.Num2 = D.Num2
> >>>> AND T.Num3 = D.Num3
> >>>> AND T.Num4 = D.Num4
> >>>> AND T.Num5 = D.Num5
> >>>> AND T.Num6 = D.Num6
> >>>
> >>> Have you run just the select portion of the insert statement with
> >>> some test data to see what is returned?
> >>>
> >>> I see 4 tables: DS, T, S, and D
> >>> I see only 2 joins: T to DS and D to DS (where is S???)
> >>>
> >>> It looks like you have a select problem, so verify the select works
> >>> correctly before you apply it to the insert operation. You should
> >>> also update your join syntax to the ANSI style. It will be more
> >>> compliant and be easier to read.
> >>>
> >>>
> >>> --
> >>> David Gugick
> >>> Imceda Software
> >>> www.imceda.com
> >
> > You can wait on the ANSI conversion. Just get the select working
properly
> > first using the old style syntax. It's just that the ANSI syntax makes
it
> > more difficult to leave out joins. Generally, all your tables need to
> > appear in some join condition. If you have four tables, then you at
least
> > three joins to join them. I see a T to D join I missed earlier, but I'm
> > still not seeing a join anywhere to S. Why it that table not joined
> > anywhere?
> >
> > --
> > David Gugick
> > Imceda Software
> > www.imceda.com
>
>

Relevant Pages

  • RE: Control box value not updating table
    ... "Jeff" wrote: ... event for the control and it gives me a syntax error... ... Can this dirty flag be reset by setting it to false? ...
    (microsoft.public.access.forms)
  • RE: open a file
    ... I dont understand why nor do i understand the syntax, do i need to manipulate it? ... "jeff" wrote: ... > try having your command button call this: ... > Sub OpenMyFile() ...
    (microsoft.public.excel.misc)
  • Re: Ruby blocks in Python, a suggestion
    ... syntax and semantics w/o lengthy prose. ... Jeff> generator. ... Ruby people are arguing that using the block to do the ...
    (comp.lang.python)
  • Re: ActiveX Component
    ... > I had made several attempts at re-registering the DAO360 with no success until ... I was leaving out the quotation marks out of the syntax. ... Jeff Conrad ...
    (microsoft.public.access.forms)
  • Re: How to use openArgs with Set
    ... Jeff, ... > The syntax of the OpenForm command includes openargs so you can pass a value ... > into a form as it opens. ... is it possible to pass an Arg with using set? ...
    (microsoft.public.access.modulesdaovba)
Loading