sql チートシート(ポスグレ)

前提

登場するテーブル

• parents
  • 親
    • has_many :children
• children
  • 子
    • belogns_to :parent
    • has_many :grandchildren
• grandchildren
  • 孫
    • belogns_to :child

sql集

子が存在する親のみ取得

SELECT
    "parents".*
FROM
    "parents"
    INNER JOIN
        "children"
    ON  "parents"."id" = "children"."parent_id"

孫が存在する親のみ取得

SELECT
    "parents".*
FROM
    "parents"
    INNER JOIN
        "children"
    ON  "parents"."id" = "children"."parent_id"
WHERE
    "parents"."id" IN(
        SELECT
            "children"."parent_id"
        FROM
            "children"
        WHERE
            "children"."id" IN(
                SELECT
                    "grandchildren"."children_id"
                FROM
                    "grandchildren"
            )
    )