Getting the recordcount of a third table from a Join query

[Guest]

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>

[paross1]

Did you make it a LEFT (OUTER) JOIN as well (something like below)? Not sure about what database you are using, but the ISNULL() function in MSSQL will convert any instances where you don't have a match to a 0 for counting purposes.

SELECT r.retailer_name, r.id, r.shortname,
d.id AS wid,d.merchant_id, d.user_id, d.e_updates,
COUNT(ISNULL(c.merchant_id, 0)) AS cc_count
FROM cc_watchlists d
LEFT JOIN merchants r ON d.merchant_id=r.id
LEFT JOIN ccsample c ON d.merchant_id=c.merchant_id
WHERE d.user_id=#SESSION.Auth.ID#
GROUP BY r.retailer_name, r.id, r.shortname,
d.id,d.merchant_id, d.user_id, d.e_updates
ORDER BY r.retailer_name ASC

Phil

[Guest]

Hi Phil,

No - all the references I could find to doing a query of 3 tables actually removed any form of 'JOIN' in the statement.

[paross1]

I updated my previous post.... you might take a look.

[Guest]

Thanks Phil,

I'm getting an error running that as follows;

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 0)) AS cc_count FROM cc_watchlists d LEFT JOIN merchants r ON d.merchant_id=r' at line 3

The syntax of your code, from my limited knowledge, looks fine though - I can't spot any mis-placed characters.

[paross1 · Accepted solution]

MySQL uses IFNULL() instead of ISNULL().....

Try COUNT(IFNULL(c.merchant_id, 0)) AS cc_count

Phil

[Dan_Bracuk]

Based on your original question, the way to get the answer is by using subqueries.

select count(*)
from sometable
where somefield in
(select etc)

However, if you need the data in the subquery also, run it first. Then use a valuelist instead of a subquery to get the recordcount from the third table.

[Guest]

Thanks Phil - that has worked :-)

Dan, thanks for your input also - my understanding however is that Phil's method is the more 'performance friendly' method?