CODEWITHSUNDEEP
sqlms-access
Jossy
Jossy

Reputation: 999

How do I fix the syntax of a sub query with joins?

I have the following query:

SELECT tours_atp.NAME_T, today_atp.TOUR, today_atp.ID1, odds_atp.K1, today_atp.ID2, odds_atp.K2
FROM (players_atp INNER JOIN (players_atp AS players_atp_1 INNER JOIN (today_atp INNER JOIN odds_atp ON (today_atp.TOUR = odds_atp.ID_T_O) AND (today_atp.ID1 = odds_atp.ID1_O) AND (today_atp.ID2 = odds_atp.ID2_O) AND (today_atp.ROUND = odds_atp.ID_R_O)) ON players_atp_1.ID_P = today_atp.ID2) ON players_atp.ID_P = today_atp.ID1) INNER JOIN tours_atp ON today_atp.TOUR = tours_atp.ID_T
WHERE (((tours_atp.RANK_T) Between 1 And 4) AND ((today_atp.RESULT)="") AND ((players_atp.NAME_P) Not Like "*/*") AND ((players_atp_1.NAME_P) Not Like "*/*") AND ((odds_atp.ID_B_O)=2))
ORDER BY tours_atp.NAME_T;

I'd like to add a field to this query that provides me with the sum of a field in another table (FS) with a few criteria applied.

I've been able to build a stand alone query to get the sum of FS by ID_T as follows:

SELECT tbl_Ts_base_atp.ID_T, Sum(tbl_Ts_mkv_atp.FS) AS SumOfFS
FROM tbl_Ts_base_atp INNER JOIN tbl_Ts_mkv_atp ON tbl_Ts_base_atp.ID_Ts = tbl_Ts_mkv_atp.ID_Ts
WHERE (((tbl_Ts_base_atp.DATE_T)>Date()-2000 And (tbl_Ts_base_atp.DATE_T)<Date()))
GROUP BY tbl_Ts_base_atp.ID_T, tbl_Ts_mkv_atp.ID_Ts;

I now want to match up the sum of FS from the second query to the records of the first query by ID_T. I realise I need to do this using a sub query. I'm confident using these when there's only one table but I consistently get 'syntax errors' when there are joins.

I simplified the first query down to remove all the WHERE conditions so it was easier for me to try and error check but no luck. I guess the resulting SQL will also be easier for you guys to follow:

SELECT today_atp.TOUR, (SELECT Sum(tbl_Ts_mkv_atp.FS)
FROM tbl_Ts_mkv_atp INNER JOIN (tbl_Ts_base_atp INNER JOIN today_atp ON tbl_Ts_base_atp.ID_T = today_atp.TOUR) ON tbl_Ts_mkv_atp.ID_Ts = tbl_Ts_base_atp.ID_Ts AS tt
WHERE tt.DATE_T>Date()-2000 And tt.DATE_T<Date() AND tt.TOUR=today_atp.TOUR
ORDER BY tt.DATE_T) AS SumOfFS
FROM today_atp

Can you spot where I'm going wrong? My hunch is that the issue is in the FROM line of the sub query but I'm not sure. Thanks in advance.

Upvotes: 0

Views: 28

Answers (1)

Lee Mac
Lee Mac

Reputation: 16015

It's difficult to advise an appropriate solution without knowledge of how the database tables relate to one another, but assuming that I've correctly understood what you are looking to achieve, you might wish to try the following solution:

select 
    tours_atp.name_t, 
    today_atp.tour, 
    today_atp.id1, 
    odds_atp.k1,
    today_atp.id2,
    odds_atp.k2,
    subq.sumoffs
from 
    (
        (
            (
                (
                    today_atp inner join odds_atp on 
                    today_atp.tour = odds_atp.id_t_o and 
                    today_atp.id1 = odds_atp.id1_o   and
                    today_atp.id2 = odds_atp.id2_o   and
                    today_atp.round = odds_atp.id_r_o
                )
                inner join players_atp as players_atp_1 on
                players_atp_1.id_p = today_atp.id2
            ) 
            inner join players_atp on 
            players_atp.id_p = today_atp.id1
        )
        inner join tours_atp on 
        today_atp.tour = tours_atp.id_t
    )
    inner join
    (
        select 
            tbl_ts_base_atp.id_t, 
            sum(tbl_ts_mkv_atp.fs) as sumoffs
        from 
            tbl_ts_base_atp inner join tbl_ts_mkv_atp on 
            tbl_ts_base_atp.id_ts = tbl_ts_mkv_atp.id_ts
        where 
            tbl_ts_base_atp.date_t > date()-2000 and tbl_ts_base_atp.date_t < date()
        group by 
            tbl_ts_base_atp.id_t
    ) subq on
    tours_atp.tour = subq.id_t
where 
    (tours_atp.rank_t between 1 and 4) and 
    today_atp.result = "" and 
    players_atp.name_p not like "*/*" and 
    players_atp_1.name_p not like "*/*" and 
    odds_atp.id_b_o = 2
order by 
    tours_atp.name_t;

Upvotes: 1

Related Questions