SQL query/join syntax

  • Thread starter Thread starter Spektre
  • Start date Start date
Spektre said:
Ken,

SUCCESS!!

sqlStr = "SELECT publishers.PubName, COUNT(*) FROM (((collection INNER
JOIN
issues ON collection.IssueID = issues.ID) INNER JOIN series ON
issues.SeriesID = series.ID) INNER JOIN publishers ON publishers.ID =
series.PubID) GROUP BY publishers.PubName ORDER BY COUNT(*)"

Works great...
Click to expand...

Congratulations for persevering to the solution!

Could still use some help on the "Episodes not collected" query
Click to expand...

Try this query (based on your updated table structure information):

SELECT Publisher.ID, Publisher.PubName,
Series.ID, Series.Bk_Name, Issues.ID
FROM ((Publisher INNER JOIN Series
ON Publisher.ID = Series.PubID) INNER JOIN
Issues ON Series.ID = Issues.SeriesID)
LEFT JOIN [Collection] ON
Issues.ID = [Collection].IssueID
WHERE [Collection].IssueID IS NULL
AND Series.ID IN
(SELECT DISTINCT I.SeriesID
FROM Issues AS I INNER JOIN
[Collection] AS C ON
I.ID = C.IssueID)
 
Back
Top