Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506

    Unanswered: problem in Joins

    Hi everybody,
    One of a query was behaving strangely.It was not doing what it was intended for.

    SELECT
    [Policy].PrintInsuredCopy
    ,[Policy].PrintAgentCopy
    ,[Policy].PrintHomeOfficeCopy
    ,[Policy].policynumber
    ,[Policy].PolicySequence
    ,[Policy].DecTypeDescription
    ,[Policy].LastEffectiveDate
    ,[Policy].EndorsementReason
    ,[Policy].agentid
    ,[Agent].[Name]
    ,[Policy].InceptionDate
    ,[Policy].ExpirationDate
    ,[Operator].DriversLicenseNumber
    ,[Vehicle].Territory
    ,[Vehicle].Class
    ,[Policy].BILimit
    ,[Policy].PDLimit
    ,[Vehicle].BIPrem
    ,[Vehicle].PDPrem
    ,[Vehicle].UMBIPrem
    ,[Vehicle].UIMBIPrem
    ,[Vehicle].HighwayTax
    ,[Vehicle].RecoupPrem
    ,[Vehicle].ExpenseFee
    ,[Vehicle].BIPrem
    ,[Vehicle].PDPrem
    ,[Vehicle].UMBIPrem
    ,[Vehicle].UIMBIPrem
    ,[Vehicle].HighwayTax
    ,[Vehicle].RecoupPrem
    ,[Vehicle].ExpenseFee
    ,[Policy].EndorsementForms
    ,[Policy].FinanceID
    ,[Code].[Control]

    FROM [Policy]
    inner join [Agent]
    on [Agent].[AgentID] = [Policy].[agentid]

    inner join [Operator]
    on [Operator].[PolicyNumber]=[Policy].[Policynumber]
    AND [Operator].[PolicySequence]=[Policy].[PolicySequence]

    inner join [Vehicle]
    on [Vehicle].[PolicyNumber]=[Policy].[Policynumber]
    AND [Vehicle].[PolicySequence]=[Policy].[PolicySequence]

    inner join[Violation]
    on [Violation].[PolicyNumber]=[Policy].[Policynumber]
    AND [Violation].[PolicySequence]=[Policy].[PolicySequence]
    AND [Violation].[BegDate]<=[Policy].[InceptionDate]

    inner join [Code]
    on [Code].[Type] = 'VSC'
    AND [Code].[Code] = [Violation].[IncidentCode]

    WHERE [Policy].[PrintInsuredCopy] > 0
    AND [Policy].[CompanyID] = 1 AND [Policy].[LineID] = 1 OR [Policy].[LineID] = 2

    ORDER BY [Policy].[PolicyNumber], [Policy].[PolicySequence]

    For some reason that line is not getting working...I mean the data which contains [Policy].[PrintInsuredCopy] = 0 is also getting included in the resultset.
    Am I missing something???

    Thanks !!
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    [Policy].[PrintInsuredCopy] > 0
    AND [Policy].[CompanyID] = 1 AND [Policy].[LineID] = 1
    OR
    [Policy].[LineID] = 2



    This make things any clearer ???
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    [Policy].[PrintInsuredCopy] > 0
    AND [Policy].[CompanyID] = 1 AND ([Policy].[LineID] = 1 OR [Policy].[LineID] = 2)


    I guess this is what you need
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    WHERE [Policy].[PrintInsuredCopy] > 0
    AND [Policy].[CompanyID] = 1
    AND [Policy].[LineID] IN ( 1 , 2 )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Thanks to Enigma and Rudy both of you. I am sorry for replying late.As a matter of fact I have relocated to Bangalore, so I was quite busy for that for the last few days.I will catch you people very soon.
    Have a nice day guys!!
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I thought I remembered you as from Kolkata (sp?). I forget the new name of that city.

  7. #7
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by MCrowley
    I thought I remembered you as from Kolkata (sp?). I forget the new name of that city.
    Hi MCrowley,
    Nice to hear from you...
    Kolkata is the new name of Calcutta.Yea ,I am from Kolkata...my native place ..but I was relocated to Mumbai.Now again to Bangalore.Bangalore is the graden city of India and also the silicon valley.The weather is really outstanding through out the year...cool and pleasant..I am loving this place..
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •