CF/SQL & QofQ - How to join records

In Coldfusion, I have two datasources: dwq and prq. I'm querying dwq for a news_id that will be compared against the pageid in prq.

<cfquery name="dwq" datasource="cu_dsn">
 SELECT  NEWS_ID 
 FROM  dw 
</开发者_运维百科cfquery>

<cfquery name="prq" datasource="commonspot">
 SELECT 
     PAGEID, 
        TITLE
 FROM pr
 WHERE (PUBLISH ='Yes' OR PUBLISH=NULL)
</cfquery>

<cfquery name="join" dbtype="query">
 SELECT
        prq.PAGEID, 
        prq.TITLE
     dwq.NEWS_ID
 FROM prq, dwq
 where prq.pageid <> dwq.news_id
</cfquery>

What I need to do is select all records in the prq datasource that do not match a news_id. Since I cannot use JOIN in QofQ, I was hoping to substitute a WHERE clauses, but the SQL is returning one record multiple times (equal to the # of records in dwq).

Could someone please assist with the proper SQL to use? Thank you!!

---

If the two tables dw and pr are both available via the same datasource (your question suggests they are not) then Spiny Norman's first approach is the way to go.

If not, you could do something similar by replacing your second query (prq) with:

<cfquery name="prq" datasource="commonspot">
 SELECT 
     PAGEID, 
     TITLE
 FROM pr
 WHERE (PUBLISH ='Yes' OR PUBLISH=NULL)
     AND PAGEID NOT IN (#valuelist(dwq.news_id)#)
</cfquery>

You wouldn't then need your third query (join). Not sure how well this would perform with large numbers of rows in dw though, and your DBMS may have a limit on the number of IDs it will accept in an IN.

---

Well, I don't really know anything about CF or QofQ, but in regular SQL, to get all records from prq that do not match a certain news_id you would use:

SELECT
    prq.PAGEID,
    prq.TITLE
FROM prq
WHERE prq.pageid <> {dwq.news_id} -- That is, insert the proper news_id here in the correct coldfusiony way

If you want to get all prq's that don't match any dwq.news_id (all pages that are not news pages, I guess), use:

SELECT
    prq.PAGEID,
    prq.TITLE
FROM prq
WHERE prq.pageid NOT IN (
    SELECT NEWS_ID
    FROM dwq
)

HTH, And good luck. Maybe I'll try to find out how to do this in CF later. Be sure to post back when you find out how to get it to work!

EDIT: Ah, here we go: if you want to loop over the news_ids in query 1 and get all pageids that are not that news id (maybe for a list of links to other items?), use:

<cfloop query="dwq">
    <cfquery name="prq" datasource="commonspot">
        SELECT 
            PAGEID, 
            TITLE
         FROM pr
         WHERE (PUBLISH ='Yes' OR PUBLISH=NULL)
           AND PAGEID <> #dwq.NEWS_ID#
    </cfquery>

    <cfdosomethingwithquery query="prq">
        ...
    </cfdosomethingwithquery>
</cfloop>

I think. This would probably use a lot more queries than necessary, though.