Query help

[Guest]

I have run a query, but it took me multiple steps to get the answer I wanted.
I was wondering if there was a way I could of gotten the answer in one
simple query.

Say you have a database of cataloged books, with all the book names etc in a
table. Then in another table you have a list of different libraries, that
have these books. Is there a way to find out which books are present in
library A AND library B. The book must exist in both places for it to give
me that book title.

I would appreciate any ideas to simplify this query, which I am sure is VERY
common.

 

[Guest]

You could help us to help you by giving the actual table and field names.
Also the SQL for the queries that you have so far. Open the queries in design
view. Next go to View, SQL View and copy and past it here. Information on
primary keys and relationships would be a nice touch too.

If you are only looking for A or B, something like below might work. You may
need to link the Books table.

SELECT libraries.library
FROM libraries
WHERE libraries.Book="B"
AND libraries.library IN (SELECT libraries.library
FROM libraries
WHERE libraries.Book="A")
ORDER BY 1 ;

 

[Van T. Dinh]

I assume that you have the correct Table Structure to handle the
Many-to-Many relationship between entities Library and Book , i.e. you have
a Link / Resolver Table besides the entity Tables.

In this case, you can use an INNER JOIN to a SubQuery like:

========
SELECT B.BookID, B.Title
FROM
( tblBook AS B INNER JOIN
tblLibraryBook AS LB_Copy1 ON B.BookID = LB_Copy1.frg_BookID
) INNER JOIN
( SELECT LB_Copy2.frg_BookID
FROM tblLibraryBook AS LB_Copy2
WHERE (LB_Copy2.frg_LibraryID = {IDForLibraryB})
) AS BooksInLibraryB
ON B.BookID = BooksInLibraryB.frg_BookID
WHERE (LB_Copy1.frg_LibraryID = {IDForLibraryA})
========

 

[Dale Fye]

Assuming you have a LibraryBooks table (or something like it) that contains
both the Library and the Book, then this is the easiest solution to date.

SELECT L.Book
From LibraryBooks L
WHERE L.Library = "A" or L.Library = "B"
GROUP BY L.Book
HAVING COUNT(L.Library) = 2

HTH
Dale