SQL join issue clarification
SELECT cl.company
FROM   clients cl
       INNER JOIN supportstatus su
         ON cl.clientid = su.clientid
WHERE  su.agreement11 = 0
       AND su.status <> 'disabled'
ORDER  BY cl.company

<cfquery name="qryPendingAgreement" datasource="support">
SELECT clientid
FROM supportstatus
WHERE agreement11 = 0 AND status <> 'disabled'
</cfquery>

<cfquery name="qryClient" datasource="support">
SELECT     clientid, company, state, serv_billing
FROM         clients
WHERE prod_arth = 1 OR prod_artr = 1 OR prod_epcr_host = 1 OR prod_epcr_remote = 1 OR prod_billing = 1 OR prod_collections = 1
</cfquery>

<cfquery name="qryResults" dbtype="query">
SELECT qryClient.company, qryClient.state, qryClient.serv_billing
FROM qryPendingAgreement, qryClient
WHERE qryPendingAgreement.clientid = qryClient.clientid
order by qryClient.company
</cfquery>

SELECT cl.company
FROM   clients cl
       INNER JOIN supportstatus su
         ON cl.clientid = su.clientid
WHERE  su.agreement11 = 0
       AND su.status <> 'disabled'
       OR prod_arth = 1      OR prod_artr = 1
       OR prod_epcr_host = 1 OR prod_epcr_remote = 1
       OR prod_billing = 1   OR prod_collections = 1
ORDER  BY cl.company

SELECT cl.company
FROM   clients cl
       INNER JOIN supportstatus su
         ON cl.clientid = su.clientid
WHERE  su.agreement11 = 0
       AND su.status <> 'disabled'
       AND ( prod_arth = 1      OR prod_artr = 1
          OR prod_epcr_host = 1 OR prod_epcr_remote = 1
          OR prod_billing = 1   OR prod_collections = 1
           )
ORDER  BY cl.company

WHERE  su.agreement11 = 0
    AND isnull(su.status,'') <> 'disabled'

DECLARE @client TABLE
    (
      clientid int,
      company VARCHAR(50)
    )
    DECLARE @supportstatus TABLE
    (
      clientid int,
      [status] VARCHAR(50),
      agreement INT
    )

    INSERT INTO @client ([clientid],[company]) VALUES ( 0,'acme' )
    INSERT INTO @client ([clientid],[company]) VALUES ( 1,'byron' )
    INSERT INTO @client ([clientid],[company]) VALUES ( 2,'cathode' )

    INSERT INTO @supportstatus ([clientid],[status], agreement) VALUES ( 0, 'disabled',0)
    INSERT INTO @supportstatus ([clientid],[status], agreement) VALUES ( 0, 'disabled',1)
    INSERT INTO @supportstatus ([clientid],[status], agreement) VALUES ( 0, 'somethingelse',0)
    INSERT INTO @supportstatus ([clientid],[status], agreement) VALUES ( 0, 'somethingelse',1)

    INSERT INTO @supportstatus ([clientid],[status], agreement) VALUES ( 1, 'disabled',0)
    INSERT INTO @supportstatus ([clientid],[status], agreement) VALUES ( 1, 'disabled',1)
    --INSERT INTO @supportstatus ([clientid],[status], agreement) VALUES ( 1,   'somethingelse',0)
    --INSERT INTO @supportstatus ([clientid],[status], agreement) VALUES ( 1,   'somethingelse',1)

    --INSERT INTO @supportstatus ([clientid],[status], agreement) VALUES ( 2,   'disabled',0)
    INSERT INTO @supportstatus ([clientid],[status], agreement) VALUES ( 2, 'disabled',1)
    --INSERT INTO @supportstatus ([clientid],[status], agreement) VALUES ( 2,   'somethingelse',0)
    INSERT INTO @supportstatus ([clientid],[status], agreement) VALUES ( 2, 'somethingelse',1)

    -- all companies who have su.agreement = 0 and su.status <> 'disabled'
    SELECT cl.company
    FROM @client cl
    JOIN @supportstatus su ON cl.clientid = su.clientid
    WHERE su.agreement = 0 AND su.status <> 'disabled'
    Order By cl.company