select columns from multiple tables using nested query in sqlite3

Question

I have 3 tables - lists, items, list_items in my SQLite3 database. The table list_items is a many-to-many table comprising of following columns - list_id, item_id, quantity - where list_id is from lists table, item_id is from items table and quantity is a new field. In my application, I would like to display item names and their corresponding quantities for a given list. I am able to fetch all the items in a given list using this SQL query:

select item_name from items where item_id in (select item_id from list_items where list_id=1)

But I am not aware of how to fetch the column quantity from list_items along with the names in the above query. Can someone please help me fixing this query?

Fahmi · Accepted Answer · 2018-12-27 10:05:12Z

1

You can try below - using join

select item_name,quantity
from items i inner join list_items li on i.item_id =li.item_id 
where list_id=1

answered Dec 27, 2018 at 10:05

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Yogesh Sharma · Accepted Answer · 2018-12-27 10:05:56Z

1

Why not do the JOIN ?

select i.item_name, li.quantity   
from items i inner join
     list_items li
     on li.item_id = i.item_id
where li.list_id = 1;

answered Dec 27, 2018 at 10:05

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Collectives™ on Stack Overflow

select columns from multiple tables using nested query in sqlite3

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related