Answered
Getting the recordcount of a third table from a Join query
Hi Folks,
I've been trying to get my head around the best way of doing this within a single query, and sadly none of the books within my collection touch upon this scenario.
I'm using the query below to query 2 tables as you can see. I want to add in a further query which searches a third table 'ccsample' looking for matching records in the 'merchant_id' field against the merchant_id field of the cc_watchlists table. I only want to return the recordcount from the 'ccsample' table.
I've tried adding another join, however if the recordcount in ccsample is 0, no records are returned at all (the query below returns 2 rows).
I hope this makes sense!
Query;
<cfquery name="getwatched" datasource="#datasource#">
SELECT r.retailer_name, r.id, r.shortname, d.id AS wid, d.merchant_id, d.user_id, d.e_updates
FROM cc_watchlists d
LEFT JOIN merchants r
ON d.merchant_id=r.id
WHERE d.user_id=#SESSION.Auth.ID#
ORDER BY r.retailer_name ASC
</cfquery>
I've been trying to get my head around the best way of doing this within a single query, and sadly none of the books within my collection touch upon this scenario.
I'm using the query below to query 2 tables as you can see. I want to add in a further query which searches a third table 'ccsample' looking for matching records in the 'merchant_id' field against the merchant_id field of the cc_watchlists table. I only want to return the recordcount from the 'ccsample' table.
I've tried adding another join, however if the recordcount in ccsample is 0, no records are returned at all (the query below returns 2 rows).
I hope this makes sense!
Query;
<cfquery name="getwatched" datasource="#datasource#">
SELECT r.retailer_name, r.id, r.shortname, d.id AS wid, d.merchant_id, d.user_id, d.e_updates
FROM cc_watchlists d
LEFT JOIN merchants r
ON d.merchant_id=r.id
WHERE d.user_id=#SESSION.Auth.ID#
ORDER BY r.retailer_name ASC
</cfquery>
AdChoices