Counts from multiple tables

[mr_doles]

I have four tables: book_inventory, cd_inventory, movie_inventory, and
lu_location with a relationship on location/purchase_location fields.
I want to create a query for a report that pulls back a count of each
type from a location (i.e. 3 books, 1 movie, 2 CDs from library; 0
books, 2 movies, 5 CDs from Pawn Shop, etc...)
Using a LEFT JOIN gives me incorrect info and INNER JOIN gives me no
info (due to small sample data). This is only step 1 as I want to also
add a WHERE clause using prices and dates. Below is what I am using
now.

SELECT lu_location.location, Count(book_inventory.pk_book_id) AS Books,
Count(cd_inventory.pk_cd_id) AS CDs, Count(movie_inventory.pk_movie_id)
AS Movies
FROM ((lu_location LEFT JOIN book_inventory ON lu_location.location =
book_inventory.purchase_location) LEFT JOIN cd_inventory ON
lu_location.location = cd_inventory.purchase_location) LEFT JOIN
movie_inventory ON lu_location.location =
movie_inventory.purchase_location
GROUP BY lu_location.location
ORDER BY lu_location.location;

I guess a subquery would be the way to go but I can't figure out that
either. Can this even be done. I could also use subreports but it
does not look as clean.

 

[Tom Ellison]

Dear Todd:

SELECT location, "Location" AS Source, Count(*) AS Books
FROM lu_location
GROUP BY location
UNION ALL
SELECT location, "Book" AS Source, Count(*) AS Books
FROM book_inventory
GROUP BY location
UNION ALL
SELECT location, "CD" AS Source, Count(*) AS Books
FROM CD_inventory
GROUP BY location
UNION ALL
SELECT location, "Movie" AS Source, COUNT(*) AS Books
FROM movie_inventory
GROUP BY location

The above combines the source tables as though they were all one. A JOIN
would not be what you need at all.

Tom Ellison

 

[mr_doles]

That works but it duplicates the location for each select statement. I
really want three rows, one for each location with a count of type:

(Location) Library | (Book Count) 2 | (CD Count) 1 | (Movie Count) 4
(Location) Pawn Shop | (Book Count) 3 | (CD Count) 2 | (Movie Count) 0
(Location) Movie Gallery | (Book Count) 0 | (CD Count) 1 | (Movie
Count) 10

Is that possible?

 

[Tom Ellison]

Dear Todd:

For this I would recommend the subquery approach:

SELECT location,
(SELECT COUNT(*)
FROM book_inventory T
WHERE T.location = M.location)
AS BookCount,
(SELECT COUNT(*)
FROM CD_inventory T
WHERE T.location = M.location)
AS CDCount,
(SELECT COUNT(*)
FROM movie_inventory T
WHERE T.location = M.location)
AS MovieCount
FROM LocationTable

There needs to be a table of locations called LocationTable. You don't want
to rely on there being every location in the Book, CD, or movie tables.
Perhaps this is lu_location.

Tom Ellison

 

[mr_doles]

Yes lu_location is the lookup table with the locations in it; there is
a relationship in lu_lookup.location to
book_inventory.purchase_location, cd_inventory.purchase_location, and
movie_inventory.purchase location. This looks like it will work but I
have one question where is the M coming from. When I run the query it
asks for M.location as a parameter. I modified the query to this:

SELECT location,
(SELECT COUNT(*)
FROM book_inventory T
WHERE T.purchase_location = M.location)
AS BookCount,
(SELECT COUNT(*)
FROM CD_inventory T
WHERE T.purchase_location = M.location)
AS CDCount,
(SELECT COUNT(*)
FROM movie_inventory T
WHERE T.purchase_location = M.location)
AS MovieCount
FROM lu_location

I also tried M.purchase_location but it still asks for paremters for
M.purchase_location.

 

[mr_doles]

Nevermind I got it - I added FROM lu_location AS M to the end it it
gave me what I needed.

Thank you so much!!!!!!

 

[Tom Ellison]

Dear Tod:

You caught a mistake I made. Please change to this:

SELECT location,
(SELECT COUNT(*)
FROM book_inventory T
WHERE T.location = M.location)
AS BookCount,
(SELECT COUNT(*)
FROM CD_inventory T
WHERE T.location = M.location)
AS CDCount,
(SELECT COUNT(*)
FROM movie_inventory T
WHERE T.location = M.location)
AS MovieCount
FROM LocationTable M

That's where the M comes from (too bad you didn't read my mind). It's an
alias on the LocationTable, which would by your lu_lookup table as I
understand you.

Tom Ellison

 

[Tom Ellison]

You bet, Todd!!! And, you're very welcome!

Tom Ellison