Help on simple SQL statement

[roblasch]

For some reason I can't figure this out. I have a table that holds information about books called BKLIST. There is a second table called ImageLocation that holds information about the related images. I am trying to return the Title and Author fields from the BKLIST table along with a count of how many images are associated with that particular title. The two tables are related through the RecordID field in BKLIST and the BookID field in ImageLocation. The statement is based on a search so there is a having clause. The following statement works if the title has associated images, but returns nothing if there are no images. I want to return all the titles, even if the image count is 0. Any help would be appreciated!


SELECT BKLIST.TITLE, BKLIST.AUTHOR, COUNT(ImageLocation.BookID)
AS numImages
FROM BKLIST INNER JOIN
ImageLocation ON
BKLIST.Record# = ImageLocation.BookID
GROUP BY BKLIST.TITLE, BKLIST.AUTHOR
HAVING (BKLIST.TITLE LIKE N'%USSR%')

 

[aalmeida]

The following statement may not be the most optimazed one but seems to do what you need:

SELECT B.TITLE,
B.AUTHOR,
0 as numImages
INTO #A
FROM BKLIST B
GROUP BY B.TITLE, B.AUTHOR
HAVING (B.TITLE LIKE N'%USSR%')

UPDATE #A set numImages = (SELECT isnull(COUNT(ImageLocation.BookID), 0)FROM ImageLocation)
FROM BKLIST, ImageLocation
where BKLIST.Record = ImageLocation.BookID

DROP TABLE #A
Let me know AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[tlbroadbent]

Here is another solution.

SELECT
b.TITLE, b.AUTHOR, Count(i.BookID) As NumImmages
FROM BKLIST As b
LEFT JOIN ImageLocation As i
ON b.[Record#] = i.BookID
WHERE b.TITLE LIKE N'%USSR%'
GROUP BY BKLIST.TITLE, BKLIST.AUTHOR Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[aalmeida]

My bad there was a mistake on the code I wrote above, the correct code is now below:

SELECT B.TITLE,
B.AUTHOR,
B.[Record#],
0 as numImages
INTO #A
FROM BKLIST B
GROUP BY B.TITLE, B.AUTHOR, B.Record
HAVING (B.TITLE LIKE N'%USSR%')

UPDATE #A set numImages = (SELECT isnull(COUNT(I.ImageId), 0)
FROM ImageLocation I
WHERE #A.[Record#] = I.BookID)
FROM ImageLocation
WHERE #A.[Record#] = ImageLocation.BookID

SELECT TITLE, AUTHOR, numImages FROM #A

DROP TABLE #A

Terry's code it seems to me that would be faster the only thing would be the line GROUP BY should read: GROUP BY b.TITLE, b.AUTHOR . Terry the way you had it is giving me an error:
Server: Msg 107, Level 16, State 3, Line 64
The column prefix 'BKLIST' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 64
The column prefix 'BKLIST' does not match with a table name or alias name used in the query.
AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[tlbroadbent]

Al,

Thanks for the correction. You are correct - the alias use must be consistent through the entire query. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.