Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2007
    Posts
    1

    Unanswered: Basic Problem with Table Joins

    Hi All,

    I've got two tables: programs and agency. On one ASP page I've listed all the agencies, which are linked to a details page by passing the agency.agency_ID variable.

    On the details page, the SQL statement is failing. What I want to do is list all programs associated the agency the user clicked on by passing the agency_ID variable

    Here is the SQL I'm using that isn't working:

    SELECT mcaa.agency.agency_ID, mcaa.agency.agencyName,mcaa.programs.program_ID,mc aa.programs.programName,mcaa.programs.agencyID,mca a.programs.programBrief,mcaa.programs.programGroup ,mcaa.programs.directURL
    FROM mcaa.programs,mcaa.agency
    WHERE MMColParam = mcaa.agency.agency_ID AND mcaa.agency.agency_ID = mcaa.programs.agencyID
    ORDER BY programName ASC

    I really just want to display just those records where the agency_ID passed equals the agencyID.

    Any thoughts?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The thread title uses the word "Joins" but that SQL statement doesn't contain any (which won't help the matter!)

    Let us re-write this
    Code:
    SELECT a.agency_ID
         , a.agencyName
         , p.program_ID
         , p.programName
         , p.agencyID
         , p.programBrief
         , p.programGroup
         , p.directURL
    FROM   mcaa.programs As [p]
     INNER
      JOIN mcaa.agency As [a]
        ON a.agency_ID = p.agencyID
    WHERE  a.agency_ID = MMColParam
    ORDER
        BY programName ASC
    The only thing left to do is replace the bit highlighted above in orange with the selected agency_id. This will all depend on how you're accessing your data and storing/accessing the variable
    Perhaps a good suggestion would be to build the query up dynamically like so:-
    Code:
    <%
      Dim sql
    
      sql = ""
      sql = sql & " SELECT a.agency_ID, a.agencyName, p.program_ID, p.programName"
      sql = sql & " , p.agencyID, p.programBrief, p.programGroup, p.directURL"
      sql = sql & " FROM mcaa.programs As [p]"
      sql = sql & " INNER JOIN mcaa.agency As [a]"
      sql = sql & " ON a.agency_ID = p.agencyID"
      sql = sql & " WHERE a.agency_ID = "
      sql = sql & Request.QueryString("agency_id")
      sql = sql & " ORDER BY programName ASC"
    
      Response.Write(sql)
    %>
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    The thread title uses the word "Joins" but that SQL statement doesn't contain any
    does too

    it uses an inner join

    it does not happen to use JOIN syntax, but it does have a join

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quite correct actually!

    However; the query written by the OP and the one by me will produce the same results but they will perform differently (JOIN before WHERE (?)).

    Rudy, did I mention that I love the fact you're so finickity
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    Rudy, did I mention that I love the fact you're so finickity
    thank you, but i am not as "finickity" as a machine

    and they will not perform differently

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Aha! You are quite right, I'll eat my words on that one!
    After some analysis I can't find a single thing different in the execution.

    +1up to Rudy
    George
    Home | Blog

Posting Permissions

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