CommonCode.sql

SELECT domain.DomainName
	,domain.Note
	,ccm.Code
	,ccm.PriorityNo
	,ccd.TEXT
FROM CommonCodeMaster AS ccm
INNER JOIN CommonCodeDetail AS ccd ON ccm.CommonCodeMasterId = ccd.CommonCodeMasterId
INNER JOIN CommonCodeDomain AS domain ON ccm.CommonCodeDomainId = domain.CommonCodeDomainId
WHERE domain.DomainName = 'AssortmentType'
	AND ccm.IsActive = 1
	AND ccd.CountryId = (SELECT CountryId FROM Country WHERE Code = 'dk')
ORDER BY PriorityNo
	,code;
	
DECLARE @id UNIQUEIDENTIFIER = '38D18B78-27F8-4C95-9494-60E5E86D2B74';

SELECT domain.DomainName
	,domain.Note
	,ccm.Code
	,ccm.PriorityNo
	,ccd.TEXT
FROM Trio..CommonCodeMaster AS ccm
INNER JOIN Trio..CommonCodeDetail AS ccd ON ccm.CommonCodeMasterId = ccd.CommonCodeMasterId
INNER JOIN Trio..CommonCodeDomain AS domain ON ccm.CommonCodeDomainId = domain.CommonCodeDomainId
WHERE ccm.CommonCodeDomainId = (
		SELECT m.CommonCodeDomainId
		FROM Trio..CommonCodeMaster AS m
		WHERE (m.CommonCodeMasterId = @id)
			OR (m.CommonCodeDomainId = @id)
		)
	AND ccm.IsActive = 1
	AND ccd.CountryId = (
		SELECT CountryId
		FROM Trio..Country
		WHERE Code = 'dk'
		)
ORDER BY PriorityNo
	,code;