Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2008
    Posts
    2

    Unanswered: Resolving Inner Join Problem

    Here is my attempt to join two queries that work perfectly fine alone, but when I try to combine them, I get an error that I do not know how to resolve. The error message report mentions a missing operator. Which operator is it? Or is my syntax plain wrong? Thanks for the help.
    Attach Code

    HERE IS THE CODE:

    <cfquery name="getConsituencyResults" datasource="#request.datasource#">

    SELECT c.ConstituencyName, c.RegionCode, s.ConstituencyNo, vr.Party, VR.TotalVotes
    FROM Constituency c, PollingStation s
    Where c.ConstituencyNo = s.ConstituencyNo AND s.AgentPhoneNo IN (SELECT v.AgentPhoneNo
    FROM VoteResults v
    WHERE v.ResultCategory = 'L' AND v.Party = 'NPP' )
    GROUP BY c.ConstituencyName, c.RegionCode, s.ConstituencyNo

    INNER JOIN

    (
    SELECT v.Party AS Party, Sum(v.Votes) AS TotalVotes, s.ConstituencyNo AS ConstituencyNo
    FROM VoteResults v, PollingStation s
    WHERE v.AgentPhoneNo = s.AgentPhoneNo AND v.ResultCategory = 'L' AND v.Party = 'NPP'
    GROUP BY s.ConstituencyNo, v.Party
    ORDER by s.ConstituencyNo
    )vr ON
    s.ConstituencyNo = vr.ConsituencyNo
    </cfquery>


    Here is the error:

    [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 's.ConstituencyNo INNER JOIN ( SELECT v.Party AS Party, Sum(v.Votes) AS TotalVotes, s.ConstituencyNo AS ConstituencyNo FROM VoteResults v, PollingStation s WHERE v.AgentPhoneNo = s.AgentPhoneNo AND v.ResultCategory = 'L' AND v.Party = 'NPP' GROUP BY s.Constit'.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Or is my syntax plain wrong?
    Yes, your syntax is plain wrong.

    http://download.oracle.com/docs/cd/B...htm#sthref9697 & the search for "inner join"

    The whole Oracle Documentation set can be found at http://tahiti.oracle.com
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    abdlah, your query is all messed up

    when you post back, would you please

    1. confirm whether you are using microsoft access (as indicated by the error message) or oracle (the forum you posted in)

    2. explain what exactly you're trying to achieve and where you got that query from
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2008
    Posts
    2
    r937,
    thank for your reply, I am using ACCESS with an Oracle back-end.

    I have the following table definition: Agent(AgentPhone*, AgentName), Constituency(ConstituencyNo*,ConstituencyName, RegionCode, ConstituencyDistrict), Party(PartyCode*, PartyName), PollingStation(StationNo*,ConstituencyNo*,RegionCo de, AgentPhone, StationName), Region(RegionCode, RegionName), and VoteResults(Party*, ResultsCategory*, Votes, AgentPhone)

    * Represents the fields that form the primary keys. Results Category are L and S (representing Parliamentary and Presidential results respectively).

    The INNER JOIN I was trying to write was to retrieve the total votes received by a particular party for a particular category (e.g. presidential) of votes in the reporting constituency.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT c.ConstituencyName
         , c.RegionCode
         , SUM(v.Votes) AS TotalVotes
      FROM VoteResults v
    INNER
      JOIN PollingStation s
        ON s.AgentPhoneNo = v.AgentPhoneNo 
    INNER
      JOIN Constituency AS c
        ON c.ConstituencyNo = s.ConstituencyNo
     WHERE v.ResultCategory = 'L' 
       AND v.Party = 'NPP'
    GROUP 
        BY c.ConstituencyName
         , c.RegionCode
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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