How to execute a subquery on the same table?

Question

SQL

Hello!
I conjure over the bitrix table b_iblock_element_property (its structure is below). The task is as follows: the model has 2 properties ( id_site , id_advert their identifiers for the IBLOCK_ELEMENT_ID field : 22, 23 respectively). You need to get id_advert for all elements with id_site = X.
Table structure:

spoiler

CREATE TABLE `b_iblock_element_property` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `IBLOCK_PROPERTY_ID` int(11) NOT NULL,
  `IBLOCK_ELEMENT_ID` int(11) NOT NULL,
  `VALUE` text NOT NULL,
  `VALUE_TYPE` char(4) NOT NULL DEFAULT 'text',
  `VALUE_ENUM` int(11) DEFAULT NULL,
  `VALUE_NUM` decimal(18,4) DEFAULT NULL,
  `DESCRIPTION` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `ix_iblock_element_property_1` (`IBLOCK_ELEMENT_ID`,`IBLOCK_PROPERTY_ID`),
  KEY `ix_iblock_element_property_2` (`IBLOCK_PROPERTY_ID`),
  KEY `ix_iblock_element_prop_enum` (`VALUE_ENUM`,`IBLOCK_PROPERTY_ID`),
  KEY `ix_iblock_element_prop_num` (`VALUE_NUM`,`IBLOCK_PROPERTY_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=507 DEFAULT CHARSET=cp1251

Requests:

spoiler

Запрос для выборки элементов с "id_site = 1":

SELECT *
FROM b_iblock_element_property
WHERE IBLOCK_PROPERTY_ID = 22 AND value = 1

Запрос для получения "id_advert":

SELECT DISTINCT value as id_advert
FROM b_iblock_element_property
WHERE IBLOCK_PROPERTY_ID = 23

А вот теперь интрига: как это все подружить? Подзапрос не хочет работать (вероятно проблема с "ссылками" на таблицу):

SELECT DISTINCT value as id_advert
FROM b_iblock_element_property
WHERE IBLOCK_PROPERTY_ID = 23 AND ID IN (
  SELECT t2.id
  FROM b_iblock_element_property as t2
  WHERE t2.IBLOCK_PROPERTY_ID = 22 AND t2.value = 1
)

С джойнами тоже не получается, только если использовать cross join:

SELECT DISTINCT t1.value as id_advert
FROM b_iblock_element_property as t1, b_iblock_element_property as t2
WHERE t1.IBLOCK_PROPERTY_ID = 23 AND t2.IBLOCK_PROPERTY_ID = 22 AND t2.value = 1

How can I solve this problem better?

Answer 1

SELECT t1.value as id_advert
FROM b_iblock_element_property as t1
INNER JOIN  b_iblock_element_property as t2 ON t2.value = 1 AND t2.IBLOCK_PROPERTY_ID = 22
WHERE t1.IBLOCK_PROPERTY_ID = 23
GROUP BY  t1.value

Approximately so tried to do JOIN???
By the way, I recommend forgetting the word DISTINCT and using GROUP BY

Answer 2

What did the API not please that you need to pervert with SQL?

Answer 3

SELECT DISTINCT value as id_advert
FROM b_iblock_element_property
WHERE IBLOCK_PROPERTY_ID = 23 AND ID IN (
SELECT t2.id
FROM b_iblock_element_property as t2
WHERE t2.IBLOCK_PROPERTY_ID = 22 AND t2.value = 1
)

SELECT DISTINCT value as id_advert
FROM b_iblock_element_property
INNER JOIN (
   SELECT t2.ID
  FROM b_iblock_element_property as t2
  WHERE t2.IBLOCK_PROPERTY_ID = 22 AND t2.value = 1
) AS x USING (ID)
WHERE IBLOCK_PROPERTY_ID = 23

So try