SQL-запрос с условием для внутреннего соединения
Контекст таков: машина находится в определенном месте или в каком-либо месте внутри локации. Здесь у нас есть 2 случая: машина может иметь значение в positionId или, если местоположение содержит только одну машину, она имеет 0 в качестве значения в positionId. Я написал запрос для возврата значений для серийного номера, имени, адреса, описания и идентификатора с учетом случаев существования или отсутствия позиции:
SELECT machine.`serialNumber`, location.`name`, location.`address`, product.`identifier`,
(CASE WHEN EXISTS(SELECT `description`
FROM `positions` AS pos
WHERE pos.`locationId` = `machine`.`id`)
THEN `description`
ELSE 0
END) AS existPos
FROM machine
INNER JOIN location
ON machine.`locationId` = location.`id`
INNER JOIN positions
INNER JOIN product
ON machine.`id` = product.`machineId`
WHERE machine.`id` = 5
Результат - несколько строк одних и тех же данных. Где я делаю не так?
Ответы 2
Можете ли вы попробовать следующее:
SELECT
machine.`serialNumber`,
location.`name`,
location.`address`,
product.`identifier`,
(CASE WHEN positions.Id is not null
THEN `description`
ELSE 0
END) AS existPos
FROM
machine
INNER JOIN
location
ON machine.`locationId` = location.`id`
LEFT JOIN
positions
on positions.`Id` = machine.`positionId`
INNER JOIN
product
ON machine.`id` = product.`machineId`
WHERE machine.`id` = 5
Я думаю, вы используете MySQL. Как говорит его документация,
In MySQL, JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents (they can replace each other). In standard SQL, they are not equivalent. INNER JOIN is used with an ON clause, CROSS JOIN is used otherwise.
Это означает, что ваш INNER JOIN positions на самом деле CROSS JOIN, потому что здесь нет предложения ON. Вот почему вы получаете дублированные строки. Если вы также выберете какой-либо столбец из таблицы positions, вы увидите в нем разницу.
В дополнение к этому, поскольку a machine is in a location or in a position inside a location, вы не должны использовать INNER JOIN для location и positions. В вашем коде после внутреннего соединения location все машины без locationId - FK теряются. Если это не то, что вы ожидаете, тогда обе таблицы должны быть объединены LEFT JOIN.
LEFT JOIN location
ON machine.`locationId` = location.`id`
LEFT JOIN positions
ON machine.`positionId` = positions.`id`
Еще одна вещь, которая мне кажется странной, - это WHERE pos.locationId = machine.id. Эта взаимосвязь не описана на приведенной вами диаграмме. Я догадался, что вы можете захотеть поменять его на WHERE pos.id = machine.positionId.