SQL: connection to OR in state

Question

I have 2 tables:

Devices (id (PK))
Links (id (PK), device_id_1 (FK), device_id_2 (FK))

What is a device connected by links.

I need to select all the devices associated with the data (which may be device_id_1 or device_id_2). I tried to do this with the following query:

select d2.*
from Devices as d1
left outer join Links as l on d1.id in (l.device_id_1, l.device_id_2)
left outer join Devices as d2 on d2.id in (l.device_id_1, l.device_id_2)
where d1.id = 398 and d2.id <> 398;

But as soon as I added a second JOIN, the query returns zero rows. What am I doing wrong?

Answer 1

The where clause effectively made your last left join the inner join.

To correct the movement of left join filter criteria in join criteria

select d2.*
from Devices as d1
left outer join Links as l on d1.id in (l.device_id_1, l.device_id_2)
left outer join Devices as d2 on d2.id in (l.device_id_1, l.device_id_2)
and d2.id <> 398
where d1.id = 398;

A less elegant, although generally accepted approach would be ...

select d2.*
from Devices as d1
left outer join Links as l on d1.id in (l.device_id_1, l.device_id_2)
left outer join Devices as d2 on d2.id in (l.device_id_1, l.device_id_2)
where d1.id = 398 
  and (d2.id <> 398 OR D2.ID is null)

I generally think so about it.

, , . , . , where, , , , NULLS.

, < > ... < > , , .

1 = NULL NULL, 1 < > NULL NULL; ,

Answer 2

.

, .

, , - , :

SELECT d.* (put the list of the column and not *)
FROM device d
where exists (select 1 from Links l where l.device_id_1 = d.id and l.device_id_2 = 398 OR l.device_id_1 = 398 and l.device_id_2 = d.id)



SELECT d.*
FROM Device d
WHERE EXISTS (SELECT 1 FROM Links l where l.device_id_1 = d.id and l.device_id_2 = 398)
UNION ALL
SELECT d.*
FROM Device d
WHERE EXISTS (SELECT 1 FROM Links l where l.device_id_2 = d.id and l.device_id_1 = 398)

, UNION ALL UNION, .

Answer 3

This file works and does not contain duplicates. Just run two queries and combine them together:

SELECT D.id
FROM Devices D
INNER JOIN Links L
ON D.id = L.device_id_1
WHERE D.id <> 398
AND L.device_id_2 = 398
UNION
SELECT D.id
FROM Devices D
INNER JOIN Links L
ON D.id = L.device_id_2
WHERE D.id <> 398
AND L.device_id_1 = 398

Tested here: http://sqlfiddle.com/#!9/e1269/6

Answer 4

We can drop the Devices table from the @ kbball solution

SELECT L.device_id_1
FROM Links L
WHERE L.device_id_2 = 398
   -- AND L.device_id_1!=398 if self-links are possible
UNION
SELECT L.device_id_2
FROM Links L
WHERE L.device_id_1 = 398
   -- AND L.device_id_2!=398 if self-links are possible
;