I work with sqlite. In the settings of the application it is possible to add groups to which you can then add channels. The channels table has a TEXT type field and is equal to the id of the comma separated groups. For example, groups = 1,2,3,4,5,6 . In the group settings is displayed as the name and number of channels in the group.

 SELECT g.*,COUNT(c._id) as _count FROM groups g,playlists p LEFT JOIN channels c ON p._active = 1 AND c._groups LIKE '%' || g._id || ',%' GROUP BY g._id ORDER BY g._name

Everything works well, but only if there are no records in the channels table, then no groups are displayed in the settings. Help fix the sql query so that if the channels table was empty, the groups were output and the number of channels in them was zero.

  1. CREATE TABLE IF NOT EXISTS groups (_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,name TEXT)
  2. CREATE TABLE IF NOT EXISTS playlists (_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,name TEXT,active INTEGER,count INTEGER)
  3. CREATE TABLE IF NOT EXISTS channels (_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,name TEXT,groups TEXT,playlist_id INTEGER)
  4. groups - 1, Movies
  5. groups - 2, Soaps
  6. groups - 3, News
  7. playlists - 4, My List, 1,200
  8. channels - 1.1 channel, '1,3'
  9. channels - 2, Russia, ''
  10. channels - 3, TNT, '1,3'
  • one
    Give the structure and links of the tables, and why do you join the playlists? - JVic
  • There are no connections. I think they just have nothing to do with. Groups = id, name, count. playlists = id, name, active.channels = id, name, desc, groups, playlist_id. You definitely need to join channels, but still if there is no playlist, then there will be no channels. And again, nothing will be displayed. - Ivan
  • one
    Attach create table instructions to a post and attach at least one entry in each table. So it will be easier for everyone. - Sergey Gornostaev
  • What channels? You are now playing channel playlists and will you be channeling channels? You asked the same question three days ago. And still do not bother to read the mat part? - JVic
  • 3 days ago there was another question. I am now joining a group with channels. - Ivan

1 answer 1

 SELECT g.*,COUNT(c._id) as _count FROM groups g LEFT JOIN playlists p LEFT JOIN channels c ON p._active = 1 AND c._groups LIKE '%' || g._id || ',%' ON 1 GROUP BY g._id ORDER BY g._name
  • Thank you very much for the rescue. And then I still do the project halfway, and the android studio after updating windows compiles the project after 10 times. JOIN channels c ON p._active = 1 AND c._groups LIKE '%' || g._id || ',%' GROUP BY g._id ORDER BY g._name - Ivan
  • one
    @Ivan you just need to divide tasks into simple ones. For example, do you need all the fields from the groups? Write a simple query that will return all these fields to you. Next, connect to the second table. Check that nothing is broken, including on boundary cases, that is, when the right or left table is empty. Further, the third table, while without conditions. Check it out. Then the first condition. Check, etc. Otherwise, you can make a mistake at the very beginning, and you will search at the end. - GreyGoblin
  • so sort of sharing and doing. Cleanly in the android studio I changed the request and wait for the pack to compile for good luck. Now I’ll look for the sqlite editor so it would go faster. - Ivan
  • one
    @Ivan in nete, you can find online query testers where you can create your own tablets and test requests. For example, here: w3schools.com/sql/trysql.asp?filename=trysql_delete - GreyGoblin
  • ok spasibo bydy znat - Ivan