I am working in DB Browser for SQLite. I have a table that contains devices. There are 2 types of devices I am concerned with. They can be summed up as: Select device_id from device where type = 'port' and Select device_id from device where type = 'station'

I need to create a record for each station/port combination in a second table.

Example:

device_id	type
1	port
2	Port
3	Station
4	Station

I am working in DB Browser for SQLite. I have a table that contains devices. There are 2 types of devices I am concerned with. They can be summed up as: Select device_id from device where type = 'port' and Select device_id from device where type = 'station'

I need to create a record for each station/port combination in a second table.

Example:

device_id	type
1	port
2	Port
3	Station
4	Station

Result:

station	port
3	1
4	1
3	2
4	2

I have tried Recursive CTEs, joins, unions. I either couldn't make them work or they didn't give the result i was looking for.

• sqlite

Share Improve this question Follow asked Mar 20 at 15:57 Wade WhitmoyerWade Whitmoyer 311 bronze badge

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

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).