How many times does item in one table appear in another (incl. zer

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This is probably a really simple query but the solution is escaping me.

In an adp, I need to know how many times each production line (listed in
Line table) appears in a separate Run table for each date. I have that
working as long as the line occurs at least once (using INNER Join), but I
need the line count to show even if it's zero-- so that, no matter what, each
line is represented in the output. I know a Join is not the right way to go
about this, but can't figure out what is.

Thanks for any and all help,

Randall Arnold
 
Actually a join is probably the correct way. But you would need either a
LEFT or RIGHT join not an INNER join. Without more details it is difficult
to say which. GENERIC Sample query

SELECT ProductionTable.ProductionLine, Count(LineName) as CountLines
FROM ProductionTable LEFT JOIN LineTable
On ProductionTable.ProductionLine = LineTable.ProductionLine
GROUP BY ProductionTable.ProductionLine
 
Thanks, John, that helps. I rarely use Left or Right joins so that didn't
immediately come to mind. Makes sense now that you mention it.

Randall
 
Ignore my last post-- the following changes made the difference:

SELECT DISTINCT
TOP 100 PERCENT dbo.Line.LineID,
COUNT(dbo.HourlyESNVolume.LineID) AS LineCount
FROM dbo.Line LEFT OUTER JOIN
dbo.HourlyESNVolume ON dbo.Line.LineID =
dbo.HourlyESNVolume.LineID
GROUP BY dbo.Line.LineID
HAVING (dbo.Line.LineID IS NOT NULL)
ORDER BY dbo.Line.LineID

: )

Randall
 
Looks like I celebrated too soon.

I constructed my query based on the generic logic you showed, and it's still
leaving out records where there is no line count. I should retrieve a total
of 92 records (including those where the line isn't represented in the runs
table) but i only get 41.

I'm sure I am overlooking something very basic here, but every avenue I've
tried is a dead end. Following is my actual SQL code:

SELECT DISTINCT
TOP 100 PERCENT dbo.HourlyESNVolume.LineID,
COUNT(dbo.Line.LineID) AS LineCount
FROM dbo.Line LEFT OUTER JOIN
dbo.HourlyESNVolume ON dbo.Line.LineID =
dbo.HourlyESNVolume.LineID
GROUP BY dbo.HourlyESNVolume.LineID
HAVING (dbo.HourlyESNVolume.LineID IS NOT NULL)
ORDER BY dbo.HourlyESNVolume.LineID

Thanks again,

Randall
 
Back
Top