April 4, 2016 at 3:23 pm
Hi I have a problem with selecting. I have an application, where admin can create multiple lists of books and those lists have users assigned to them and then they can choose what books, they want and they can print their selection. I have problem with displaying the list on the user part. A user can add or delete books from their selection. I think I'm missing some conditions.
Table users:
+----+-------+----------+
| id | name | class_id |
+----+-------+----------+
| 1 | John | 1 |
| 2 | Jenna | 2 |
+----+-------+----------+
Table classes
+-----+--------+
| id | name |
+-----+--------+
| 1 | class1 |
| 2 | class2 |
+-----+--------+
Table books:
+----+-------+
| id | name |
+----+-------+
| 1 | test1 |
| 2 | test2 |
| 3 | test3 |
| 4 | test4 |
| 5 | test5 |
+----+-------+
Table lists
+-----+----------+
| id | class_id |
+-----+----------+
| 1 | 1 |
| 2 | 2 |
+-----+----------+
Table records
+---------+---------+
| list_id | book_id |
+---------+---------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 4 |
| 2 | 5 |
+---------+---------+
Table selection
+---------+---------+---------+
| list_id | book_id | user_id |
+---------+---------+---------+
| 1 | 1 | 1 |
| 1 | 2 | 1 |
| 2 | 4 | 2 |
+---------+---------+---------+
I need to select the name of the books, which are not in the selection, but they are in the list
I've tried this, but it's not working, how it should:
SELECT b.id, b.name FROM records r join books b on(b.id=r.book_id) join lists l on(l.id=t.list_id) join class c on(c.id=l.class_id) join users u on(u.class_id=c.id) left join selection s on(r.book_id=s.book_id) where class_id=(select class_id from users where id=1) and r.list_id=1 and not EXISTS (select book_id from selection)
r.list_id is switched by a session
So in the case of John I would like to see test3 and in the case of Jenna test1 and test5.
And I have a problem, that if I puttest1 into John's list, it will not be displayed in Jenna's.
April 4, 2016 at 4:23 pm
Couple of things I see you're missing. Your join to Selection needs to limit by user and also, your check for non-existence for selection needed tweaked. Also, the way you did your other stuff in the where clause needed changed. Here's my mockup and examples of working queries based on the information that I understand.
--======================================================================
-- classes Contain Users
-- Lists contain classes
-- Records represent many-to-many betwen Books & Lists
-- Selections tie books & lists to users
--======================================================================
DECLARE @users TABLE (id int, name varchar(20), class_id int)
INSERT INTO @users
SELECT 1, 'John', 1 UNION ALL
SELECT 2, 'Jenna', 2
DECLARE @classes TABLE (id int, name varchar(10))
INSERT INTO @classes
SELECT 1, 'class1' UNION ALL
SELECT 2, 'class2'
DECLARE @books TABLE (id int, name varchar(10))
INSERT INTO @books
SELECT 1, 'test1' UNION ALL
SELECT 2, 'test2' UNION ALL
SELECT 3, 'test3' UNION ALL
SELECT 4, 'test4' UNION ALL
SELECT 5, 'test5'
DECLARE @lists TABLE (id int, class_id int)
INSERT INTO @lists
SELECT 1, 1 UNION ALL
SELECT 2, 2
DECLARE @records TABLE (list_id int, book_id int)
INSERT INTO @records
SELECT 1, 1 UNION ALL
SELECT 1, 2 UNION ALL
SELECT 1, 3 UNION ALL
SELECT 2, 1 UNION ALL
SELECT 2, 4 UNION ALL
SELECT 2, 5
DECLARE @selection TABLE(list_id int, book_id int, user_id int)
INSERT INTO @selection
SELECT 1, 1, 1 UNION ALL
SELECT 1, 2, 1 UNION ALL
SELECT 2, 4, 2
--==== John's Query (User 1, List 1)
SELECTb.id
, b.name
FROM@records r
join@books b on b.id = r.book_id
join@lists l on l.id = r.list_id
join@classes c on c.id = l.class_id
join@users u on u.class_id = c.id
left join @selection s on r.book_id = s.book_id
AND s.user_id = u.id
whereu.id=1
and r.list_id=1
AND s.book_id IS NULL
--==== Jenna's Query (User 2, List 2)
SELECTb.id
, b.name
FROM@records r
join@books b on b.id = r.book_id
join@lists l on l.id = r.list_id
join@classes c on c.id = l.class_id
join@users u on u.class_id = c.id
left join @selection s on r.book_id = s.book_id
AND s.user_id = u.id
whereu.id=2
and r.list_id=2
AND s.book_id IS NULL
April 5, 2016 at 2:56 am
EXISTS (or NOT EXISTS in this case) can be cleaner and more intuitive as to what it's doing over a join/is null check.
Using John's tables
SELECTb.id
, b.name
FROM@records r
join@books b on b.id = r.book_id
join@lists l on l.id = r.list_id
join@classes c on c.id = l.class_id
join@users u on u.class_id = c.id
whereu.id=1
and r.list_id=1
AND NOT EXISTS (SELECT 1 FROM @selection s WHERE r.book_id = s.book_id AND s.user_id = u.id)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply