Basic Problem with Table Joins

turn68 · #1 (**permalink**) 10-18-07, 09:08

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?

gvee · #2 (**permalink**) 10-22-07, 08: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)
%>

r937 · #3 (**permalink**) 10-22-07, 08:26

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

gvee · #4 (**permalink**) 10-22-07, 08:47

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