Joynym 3 sql tables how?

Question

SQL

Hello everyone,
I can not finish the request, help!
You need to select all customer ids that meet the requirements:
- city: both Samara and Barcelona
- country: not England
- sales > 1500
- if times (frequency of visits) is more than 2, then a 10% discount, the rest 5%
Table 1
id city sales
1 Samara 1000
1 Barcelona 700
2 Samara 100
3 London 500
3 Samara 500
3 Barcelona 500

Table 2
city country
London England
Samara Russia
Barcelona Spain

Table 3
id times
1 3
2 2
3 1

Thank you!!!!

Answer 1

In general, if id in table 1 is id from 3, then you can do this:

SELECT
    3TABLE.ID
    -- Вычисляем скидку
,   CASE
        WHEN
            TIMES   >   2
        THEN
            '20'
        ELSE
            '5'
    END             AS  DISCOUNT
FROM
    3TABLE
LEFT OUTER JOIN
    (
        -- Группируем по ИД
        -- Суммируем sales
        -- Берем более 1500
        SELECT
            ID
        ,   SUM(SALES)  AS  SALES
        FROM
            (
                -- Выбираем Samara и Barcelona
                -- Исключаем England
                -- Группируем (хотя, вот здесь наверное можно не группировать)
                SELECT
                    1TABLE.ID
                ,   SUM(1TABLE.SALES) AS  SALES
                FROM
                    1TABLE
                INNER JOIN
                    2TABLE
                ON
                    1TABLE.CITY   =   2TABLE.CITY
                GROUP BY
                    1TABLE.ID
                ,   1TABLE.CITY
                ,   2TABLE.COUNTRY
                ,   1TABLE.SALES
                HAVING
                    (1TABLE.CITY  IN  (N'Samara', N'Barcelona'))
                AND (NOT    (
                        2TABLE.COUNTRY    =   N'England'
                    ))
            )   AS  WRAP_TABLE
        GROUP BY
            ID
        HAVING        (SUM(sales) > 1500)
    )   AS  SUM_WRAP_TABLE
ON
    SUM_WRAP_TABLE.ID   =   3TABLE.ID