sql - Count associations in sqlite database query? -

I have a silicon database with three tables: notes, lists, and notes-in-list.

Both notes and lists have some additional data columns, such as an autogenerated id column and title, the note-in-lists have an association table with an autocomposition, and two foreign keys, one note ID and one Indicates list id.

I have a query that gives all the notes in a list:

  select _id, include notes in the title notes from Notes_In_Notes on the list. _id = notes_in_lists.note_id where Notes_in_Lists.list_id = 2  

This will return all the note titles and the IDs for example 2.

However, the notes can be in many lists and I should be able to tell if the note is associated with many lists. This same note_in_ list is listed several times by the note_in_list list .note_id.

Easy to do by yourself:

  Selection number (note_id) from the note_in_ list where note_ id = 2  

but let me Questions have to be added in a query and I do not know where it is starting.

edit
sample data

  note: _id title 1 "note 1" 2 "note 2" 3 "note 3" 4 "note 4" note_in_list_not_id_lid_id1 1 2 2 3 3 2 2 4 3 1 5 4 2 6 4 4 7 4 5  

The output of the sample output (list 2 For query):

  _id title numberOfflest 1 "note 1" 2 2 "note 2" 1 4 "note 4" 3  

Select

  notes. _id, NoteTitle, Mozi (Nil2.list_id) Notes Join Inner Notes_In_Listons at Nil 1 Notes ._id = NiL1.note_id Join the Inner Notes _Li 2 Notes on NIL 2 NiD = NiL2.note_id where NiL1.list_id = 2 group notes._id, notetle;  

This two might seem useless to join, but you need to basically run two questions.