Untitled
unknown
plain_text
2 years ago
844 B
15
Indexable
-- Create users table CREATE TABLE users ( userid INTEGER PRIMARY KEY, user_phone TEXT, user_email TEXT ); -- Populate with some data INSERT INTO users VALUES (1, '555-1234', 'user1@email.com'), (2, '555-2345', 'user2@email.com'), (3, '555-3456', 'user3@email.com'); -- Create notifications table CREATE TABLE notifications ( id INTEGER PRIMARY KEY, userid INTEGER, notification_text TEXT, read BOOLEAN ); -- Populate with some data INSERT INTO notifications VALUES (1, 1, 'Notification 1', 0), (2, 1, 'Notification 2', 0), (3, 2, 'Notification 3', 1), (4, 3, 'Notification 4', 1), (5, 1, 'Notification 5', 0); --Query--- SELECT u.userid, u.user_email, COUNT(n.userid) AS unread_count FROM users u LEFT JOIN notifications n ON n.userid = u.userid AND n.read = 0 GROUP BY u.userid, u.user_email
Editor is loading...