select records with Count, Sum and Sub-Query

I need to select all links from the links table whose statistics are not zero:

Table tlink: 
    linkid, linkname, userid

Table tuser: 
    userid, username

Table 开发者_开发技巧tfact: 
    factid, factorder, userid

Table tstat: 
    statid, linkid, userid

The User in tuser orders Clicks which are recorded in tfact.

The User can create several links which are recorded in tlink.

Every click on any Link is recorded in tstat.

The web-page should filter all links whose ordered clicks are used.

EG Company ABC orders 3 times 100 clicks each, making a total of 300 clicks.

Then they create several Links which are all shown till the total of 300 clicks is used.

I have tried several Queries//Sub-Queries but to no avail and would appreciate any help.

Something like this?

SELECT
    l.linkid,
    l.linkname,
    l.userid,
    u.username,
    f.factorder - s.clickCount AS clicksUnused
FROM
    tlink l
    INNER JOIN tuser u ON u.userid = l.userid
    INNER JOIN tfact f ON f.userid = l.userid
    INNER JOIN (
        SELECT
            linkid,
            userid,
            COUNT(*) AS clickCount
        FROM
            tstat
        GROUP BY
            linkid,
            userid
        ) s ON s.linkid = l.linkid AND s.userid = l.userid

@NGLN

Hi

I am sorry in not answering earlier but have been away for some days.

I think I got it working OK:

SELECT L.linkid, F.OrderSum - S.clickCount AS clicksUnused FROM tlink AS L INNER JOIN (SELECT userid, SUM(factorder) AS OrderSum FROM tfact GROUP BY userid) AS F ON F.userid = L.userid INNER JOIN (SELECT userid, COUNT(*) AS clickCount FROM tstat GROUP BY userid) AS S ON S.userid = L.userid --WHERE -- clicksUnused >= 1

only problem now that I can't filter out Links whose clicksUnused are higher than 1.

Any ideas, thanks, Mike

UPDATE Got this working

WHERE F.OrderSum > S.clickCount

Is this the correct way?