pratica-join.sql

-- 1
SELECT
    users.id,
    users.name,
    cities.name
FROM
    users
    JOIN cities ON cities.id = users.id
WHERE
    cities.name = 'Alegre';

-- 2
SELECT
    testimonials.id,
    writer.name AS writer,
    recipient.name AS recipient,
    testimonials.message
FROM
    testimonials
    JOIN users AS writer ON writer.id = testimonials."writerId"
    JOIN users AS recipient ON recipient.id = testimonials."recipientId";

-- 3
SELECT
    users.id,
    users.name,
    courses.name AS course,
    schools.name AS school,
    educations."endDate"
FROM
    users
    JOIN educations ON educations."userId" = users.id
    JOIN courses ON courses.id = educations."courseId"
    JOIN schools ON schools.id = educations."schoolId"
WHERE
    educations.status = 'finished'
    AND users.id = 30;

-- old solution without names
-- SELECT
--     users.id,
--     users.name,
--     educations."courseId",
--     educations."schoolId",
--     educations."endDate"
-- FROM
--     users
--     JOIN educations ON educations."userId" = users.id
-- WHERE
--     educations.status = 'finished'
--     AND users.id = 30;
-- CTE solution to sequential id
-- WITH
--     user_educations AS (
--         SELECT
--             ROW_NUMBER() OVER (
--                 ORDER BY
--                     users.id
--             ) AS id,
--             users.name,
--             courses.name AS course_name,
--             schools.name AS school_name,
--             educations."endDate"
--         FROM
--             users
--             JOIN educations ON educations."userId" = users.id
--             JOIN courses ON courses.id = educations."courseId"
--             JOIN schools ON schools.id = educations."schoolId"
--         WHERE
--             educations.status = 'finished'
--             AND users.id = 30
--     )
-- SELECT
--     id,
--     name,
--     course_name,
--     school_name,
--     "endDate"
-- FROM
--     user_educations;

-- 4
SELECT
    users.id,
    users.name,
    roles.name AS role,
    companies.name AS company,
    experiences."startDate"
FROM
    experiences
    JOIN users ON users.id = experiences."userId"
    JOIN roles ON roles.id = experiences."roleId"
    JOIN companies ON companies.id = experiences."companyId"
WHERE
    "userId" = 50
    AND "endDate" IS NULL;

--BONUS CHALLENGE
SELECT
    users.id,
    schools.name AS school,
    courses.name AS course,
    companies.name AS company,
    roles.name AS role
from
    applicants
    JOIN jobs ON jobs.id = applicants."jobId"
    JOIN users ON users.id = applicants."userId"
    JOIN educations ON educations."userId" = users.id
    JOIN schools ON schools.id = educations."schoolId"
    JOIN courses ON courses.id = educations."courseId"
    JOIN companies ON companies.id = jobs."companyId"
    JOIN roles ON roles.id = jobs."roleId"
WHERE
    jobs.active = true
    AND jobs."companyId" = 10;