Query Help
-
I have two tables:
Book is a table representing books - one row per book
BookCategory is a table relating a book and a category
The relationship between Book and BookCategory is 1->M. (A book can have 1 or more categories)
I can correctly query for books that have ANY of a set of categories (ex. 'A' or 'B') with the following:
SELECT fk_BookId FROM BookCategory WHERE Category IN ('A','B') GROUP BY fk_BookId
Here's a query I'm having trouble with:
I'm trying to query for books that have NONE of a set of categories (ex. 'A' or 'B') with the following:
SELECT fk_BookId FROM BookCategory WHERE Category NOT IN ('A','B') GROUP BY fk_BookId
Here is some TSQL that creates a temptable fills it and shows the query results:
drop table #BookCategory
CREATE TABLE #BookCategory (
Category CHAR(2),
FK_Book INT
 
insert into #BookCategory
(Category,FK_Book)
VALUES
('A',1)
insert into #BookCategory
(Category,FK_Book)
VALUES
('B',1)
insert into #BookCategory
(Category,FK_Book)
VALUES
('B',2)
insert into #BookCategory
(Category,FK_Book)
VALUES
('C',2)
insert into #BookCategory
(Category,FK_Book)
VALUES
('C',3)
select * from #BookCategory
select FK_BOOK from #BookCategory WHERE CATEGORY IN ('A','B') GROUP BY FK_Book
-- The following is an attempt to find books that dont fall into categories A and B
-- - the result I'm expecting is to only see one row- 3
-- but instead I see 2,3
select FK_BOOK from #BookCategory WHERE CATEGORY NOT IN ('A','B') GROUP BY FK_Book
Thanks,Rick Hodder
-
Try This.
Select
FK_Book,
Category
From
#BookCategory
Where
FK_Book
Not In (Select Distinct FK_Book From #BookCategory Where Category In ('A','B'))
-
Are you trying to get the data where it's not in Category A or Category B, or not in both of them?
BookId Category
1 A
1 B
2 A
3 B
4
With the above info, what would you want returned? (Posting tip: It helps to show sample data, table structure, and what you want returned when you post a question).
-SQLBill
-
Hi SQLBill,
Not in both of them.
4 would be the result I expected from the data you give above
And a related question: I would like to be able to query for in both of them.
>>(Posting tip: It helps to show sample data, table structure, and what you want returned when you post a question).
I did put code that creates and runs against a temporary table, shows the queries, and tells what I got vs what I was expecting
Thanks for your help
Rick
Thanks,Rick Hodder
-
I would use a self join in this case:
SELECT DISTINCT bc1.FK_BOOK
FROM #BookCategory bc1
LEFT JOIN #BookCategory bc2 on bc1.FK_BOOK = bc2.FK_BOOK AND bc2.CATEGORY IN ('A','B')
WHERE bc2.FK_BOOK IS NULL
In addition to this and Daniel's solution, another possible way to do it is with NOT EXISTS in the WHERE clause:
SELECT DISTINCT bc1.FK_BOOK
FROM #BookCategory bc1
WHERE NOT EXISTS (select FK_BOOK from #BookCategory where FK_BOOK = bc1.FK_BOOK and CATEGORY IN ('A','B'))
-
I noticed in your original post the following:
CREATE TABLE #BookCategory (
Category CHAR(2),
It could be the CHAR(2) giving you the problem. The Category is getting a blank space padded to the A or B so you need to TRIM it before you look for it IN ('A','B').
Yes?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply