Help Building SQL query

Question

Hi all. I have following 2 tables

Channels:
Id int PK
Title varchar

SubChannels:
ChannelId int
SubchannelId int

When I receive data from service I'm trying to persist it to SQL. I receive a few Channel instance, so I store it in Channels table, after that I'm trying to persist every subchannel (every channel contains list of subchannels), so, I store subchannels in the same table Channels, and store theirs Ids to SubChannel table in following way: ChannelId - SubChannelId.

So, I need to get this tree of channels with one query I'm trying to do this with this query:

 SELECT * FROM Channels 
 EXCEPT 
 SELECT Channels.Id, Channels.Title 
 FROM Channels INNER JOIN SubChannels 
 ON Channels.Id = SubChannels.SubchannelId

But I doesn't work correctly. Using MSSQL it works fine, but in my SQLite something is going wrong.

Could you please help me with this query, or advice me some other solution to store this tree in SQL.

My Channel class looks like this:

int Id
string Title
List<Channel> SubChannels

Thanks in advance

Answer 1

SQLite does not support EXCEPT

So you may try:

SELECT * FROM Channels  c1
WHERE NOT EXISTS(SELECT 1 
      SELECT Channels.Id, Channels.Title 
      FROM Channels c2 INNER JOIN SubChannels 
      ON Channels.Id = SubChannels.SubchannelId
      WHERE c1.Id = c2.Id
        AND c1.Title =c.Title)

Answer 2

Could you try:

SELECT Id, Title
  FROM Channels
EXCEPT 
SELECT Channels.Id, Channels.Title
  FROM Channels
 INNER JOIN SubChannels
    ON Channels.Id = SubChannels.SubchannelId

(ie, not selecting *)

A way to optimize it:

SELECT Id, Title
  FROM Channels
 WHERE Id NOT IN (
       SELECT DISTINCT SubchannelId
         FROM SubChannels
    )