79523560

Date: 2025-03-20 16:38:27
Score: 0.5
Natty:
Report link

What you want is basically the cartesian product of all stations to all ports.

While SQLite apparently does not support full outer joins, it accepts fine simple joins, and your request can be expressed as a self-join query, with the appropriate conditions for each instance of the device table: one filters rows where the type is port, the other, where it is station.

A full select query can look like this:

select S.device_id as station, P.device_id port
from device S join device P
where S.type = 'Station' and P.type = 'Port';

Be aware that, by default, SQLite is case sensitive with regards to text comparison. To properly handle as expected values in the type column with varied cases, you can either wrap all references to this column in queries with the function lower(), in which case the query looks like so:

select S.device_id as station, P.device_id port
from device S join device P
where lower(S.type) = 'station' and lower(P.type) = 'port';

You need to ensure that your string literals are fully lowercase for this to work.

Otherwise (and I recommend this alternative rather than the previous one), you can declare the column as case-insensitive with the collate nocase clause.

create table device (
    device_id integer primary key,
    type text collate nocase
);

Please find here a SQLFiddle with the above query in action (used to insert into a separate table).

Reasons:
  • Blacklisted phrase (1): regards
  • Long answer (-1):
  • Has code block (-0.5):
  • Starts with a question (0.5): What you
  • Low reputation (0.5):
Posted by: joH1