CODEWITHSUNDEEP
androidsqldatabasesqlite
user3252599
user3252599

Reputation: 821

SQLite select data from multiple tables

I need to fetch data from multiple tables but cannot make a query for that. Any help in this regard will be appreciated. There are some questions already posted about how to fetch data from multiple tables, but somehow i cannot grab the concept behind such query. Therefore i am posting question to ask if someone can build a query for me with the following details:

Tables:

CREATE TABLE users(id INTEGER PRIMARY KEY,user_name TEXT)
CREATE TABLE category(id INTEGER PRIMARY KEY,name TEXT,user_id INTEGER)
CREATE TABLE item(id INTEGER PRIMARY KEY,name TEXT,category_id INTEGER)
CREATE TABLE Used(id INTEGER PRIMARY KEY,frequency REAL,item_id INTEGER)

A user may have multiple categories and each category may have multiple items, and each item may have multiple frequency of use.

I want to select the following:

Thanks.

Upvotes: 1

Views: 2502

Answers (1)

sagi
sagi

Reputation: 40481

So you want something like this:

SELECT u.user_name,c.name,i.name,used.frequency
FROM users u
LEFT OUTER JOIN category c 
 ON(u.id = c.user_id)
LEFT OUTER JOIN item i
 ON(i.category_id = c.id)
INNER JOIN Used
 ON(i.id = used.item_id)
WHERE u.user_id = YOUR_USER_HERE

Upvotes: 1

Related Questions