CODEWITHSUNDEEP
androidsqlite
developKinberg
developKinberg

Reputation: 383

SQLite query on multiple tables with reference Id table

I need help by a little SQLite problem, in my App a user can add some todos and connect this todos with contacts , but a contact have multiple connections for multiple todos. My question is how must look my query if I want all contacts for a specific todo ?

Todos

todoId  todoTitle 
0        do some
1        do some

Contacts

contactId contactName
299             Stan
231             Jeff

ReferenceIds

 contactId  todoId
  299         0
  299         1
  231         0

Upvotes: 0

Views: 129

Answers (2)

Karakuri
Karakuri

Reputation: 38605

Assuming you have such a many-to-many relationship, you can get all contacts for a specified todo ID with a query like this one:

SELECT contactId, contactName
FROM contacts
JOIN referenceIds ON referenceIds.contactId = contacts.contactId
WHERE todoId = ID

Note that the above query didn't select any columns from the todos table. If you also want columns from that table, you need a second join like so:

SELECT contactId, contactName, todoTitle
FROM contacts
JOIN referenceIds ON referenceIds.contactId = contacts.contactId
JOIN todos ON todos.todoId = referenceIds.todoId
WHERE todoId = ID

Upvotes: 1

Anton Pogonets
Anton Pogonets

Reputation: 1162

Firstly if you want one-to-many relation you don't need relations table. It is necessary for many-to-many. If 1 contact have many todos and each todo have one contact - just add contactId to todos table. In this case you can simply select from Todos table by contactId

In your case you should make query joins

select distinct c.* 
from Contacts as c
inner join ReferenceIds as r on c.contactId = r.contactId
where todoId = ?

Upvotes: 1

Related Questions