mysql_help.sql

SELECT
	merchants.vendor_id,
	merchants.is_disconnected,
	merchants.gmc_account_id,
	merchants.created_at,
	ads.google_ads_account_id,
	account_issues.issue_id,
	account_issues.severity,
	account_issues.title,
	account_issues.updated_at AS IssueUpdatedAt
FROM (
	SELECT *
	FROM `merchants`
	WHERE merchants.deleted_at IS NULL
	ORDER BY created_at DESC
	LIMIT 20 OFFSET 20
) as merchants
LEFT JOIN ads ON ads.id = merchants.vendor_ads_account_id
LEFT JOIN (
	SELECT
		gmc_account_id,
		issue_id,
		severity,
		title,
		updated_at,
		MAX(CASE
		WHEN severity = "critical"
		THEN 3
		WHEN severity = "error"
		THEN 2
		WHEN severity = "suggestion"
		THEN 1
		ELSE 0
		END) AS severity_order
	FROM `account_issues`
	GROUP BY `gmc_account_id`
	LIMIT 1
) AS account_issues ON account_issues.gmc_account_id = merchants.gmc_account_id