SQLite, calculate foreign key references

I have two tables, one defines the list, and the other defines the items in the list. When I run this, I only get a list of items that reference them. I really need Get the results of all the rows in the list and the number of rows that reference each list in the items table.

SELECT name, COUNT(items.listId) as itemCount
FROM lists
INNER JOIN items
ON lists._id = items.listId
GROUP BY items.listId

Any help will be greatly appreciated.

Try to change INNER JOIN to LEFT OUTER JOIN, and change GROUP BY to lists._id. I haven’t tried this!

I have two tables, one defines the list, and the other defines the items in the list. When I run this, I only get the list containing the items that reference them. I really need Get the results of all the rows in the list and the number of rows that reference each list in the items table.

SELECT name, COUNT(items.listId) as itemCount
FROM lists
INNER JOIN items
ON lists._id = items.listId
GROUP BY items.listId

Any help will be greatly appreciated.

Try to change INNER JOIN to LEFT OUTER JOIN, and change GROUP BY to lists._id. I haven’t tried this!

WordPress database error: [Table 'yf99682.wp_s6mz6tyggq_comments' doesn't exist]
SELECT SQL_CALC_FOUND_ROWS wp_s6mz6tyggq_comments.comment_ID FROM wp_s6mz6tyggq_comments WHERE ( comment_approved = '1' ) AND comment_post_ID = 885 ORDER BY wp_s6mz6tyggq_comments.comment_date_gmt ASC, wp_s6mz6tyggq_comments.comment_ID ASC

Leave a Comment

Your email address will not be published.