Как я могу передать результаты запроса/подзапроса другому запросу и ОБЪЕДИНИТЬ результаты вместе?

Question

ОТВЕТ! CTE — лучший метод, использующий описательные имена фиктивных переменных.

У меня есть две соответствующие таблицыconceptкоторый описывает все общие концепции базы данных (concept_code) из разных словарей (vocabulary_id) и связывает их с уникальным стандартизированным идентификаторомconcept_id, иconcept_relationshipкоторый имеет только три столбца,concept_id_1,concept_id_2, иrelationship_id. Идентификатор отношения описывает, как идентификатор 1 связан с идентификатором 2. Команда базы данных пытается максимально стандартизировать понятия в один идентификатор, используя стандарты OMOP, поэтому многие идентификаторы, которые являются источником из одного словаря, переназначаются на соответствующий родительский стандартизованный идентификатор с использованием стандарта OMOP. Отношение «сопоставляется с». У меня есть наборы кодов, которые соответствуют идентификаторам, которые были определены экспертами в предметной области в конкретных словарях, которые не обязательно включают то, что команда базы данных выбрала в качестве своей стандартизированной концепции. Конечным продуктом, который мне нужен, является таблица, представляющая как коды/идентификаторы, определенные SME, так и соответствующие идентификаторы, на которые они «сопоставляются».

################################################### #######################

Новый код, который, кажется, ведет себя так, как предполагалось, использует оператор WITH:

      
WITH query1 AS (
  SELECT 
    * 
  FROM 
    concept 
  WHERE 
    concept_code IN ("398.4", "I45")
    AND (
      vocabulary_id IN ("ICD9","ICD10")
    )
)
SELECT *
FROM concept
WHERE concept_id IN (
    SELECT concept_id_2
    FROM concept_relationship cr
    INNER JOIN query1 q1 ON cr.concept_id_1 = q1.concept_id
    WHERE relationship_id = "Maps to"
)
UNION 
SELECT * FROM query1

################################################### #######################

Я пробовал запрос ниже:

      SELECT 
  foo.* 
FROM 
  (
    SELECT 
      * 
    FROM 
      concept 
    WHERE 
      concept_code IN ("398.4", "I45") 
      AND (
        vocabulary_id IN ("ICD9", "ICD10")
      )
  ) AS foo 
UNION 
SELECT 
  * 
FROM 
  concept 
WHERE 
  concept_id IN (
    SELECT 
      concept_id_2 
    FROM 
      concept_relationship cr 
      INNER JOIN foo ON cr.concept_id_1 = foo.concept_id 
    WHERE 
      relationship_id = "Maps to"
  )

Но он возвращает ошибку «INVALID ARGUMENT», я предполагаю, потому что я не могу передать подзапрос foo выше UNION в другой запрос. Есть ли более плавный способ сделать это? Я включил несколько фиктивных таблиц ниже, чтобы попытаться воспроизвести, что, похоже, работает на стороне базы данных, но не все столбцы включены для краткости.

концепция :

      | concept_id  | concept_code | vocabulary_id |
| ----------- | ------------ |---------------|
| 123         | 398.4        | ICD9          |
| 111         | I45          | ICD10         |
| 145         | 45155841     | SNOMED        |

concept_relationship:

      | concept_id_1 | concept_id_2 | relationship_id|
| -----------  | ------------ |--------------- |
| 123          | 145          | Maps to        |
| 111          | 145          | Maps to        |
| 145          | 145          | Maps to        |
| 145          | 111          | Maps from      |
| 145          | 123          | Maps from      |
| 145          | 145          | Maps from      |

      CREATE TABLE `concept` (
  `concept_id` VARCHAR NOT NULL, 
  `concept_code` VARCHAR NOT NULL, 
  `vocabulary_id` VARCHAR NOT NULL, 
  PRIMARY KEY (`concept_id`)
);

INSERT INTO concept (
  concept_id, concept_code, vocabulary_id
) 
VALUES 
  ("123", "388.4", "ICD9"), 
  ("111", "I45", "ICD10"), 
  ("145", "45155841 ", "SNOMED");


CREATE TABLE `concept` (
  `concept_id_1` VARCHAR NOT NULL, 
  `concept_id_2` VARCHAR NOT NULL, 
  `relationship_id` VARCHAR NOT NULL, 
  PRIMARY KEY (`concept_id_1`)
);

INSERT INTO concept_relationship (
  concept_id_1, concept_id_2, relationship_id
) 
VALUES 
  ("123", "145", "Maps to"), 
  ("111", "145", "Maps to"), 
  ("145", "145", "Maps to"), 
  ("145", "111", "Maps from"), 
  ("145", "123", "Maps from"), 
  ("145", "145", "Maps from");

Answer 1

Проблема в том, что вы используете foo на другой стороне союза, и он его не видит. но CTE может помочь вот так

      WITH foo AS (
  SELECT * 
  FROM concept 
  WHERE concept_code IN ("398.4", "I45") AND
        vocabulary_id IN ("ICD9", "ICD10")
)

SELECT *
FROM foo

UNION 

SELECT * 
FROM concept 
WHERE concept_id IN (
    SELECT concept_id_2 
    FROM concept_relationship cr 
    JOIN foo ON cr.concept_id_1 = foo.concept_id 
    WHERE relationship_id = "Maps to"
)

обратите внимание, что второй запрос после UNION можно переписать как

      SELECT * 
FROM concept 
JOIN concept_relationship cr ON cr.concept_id_2 = concept.conecpt_id
                            AND cr.relationship_id = "Maps to"
JOIN foo on cr.concept_id_1 = foo.concept_id

с помощью соединений.