Issue with JOIN relationship

[jasonhuibers]

ClientCode 223345 has 2 records in the Event table RecordNum 1 and RecordNum 2. RecordNum 1 has a record in the EventHistory table but no records in EventHistory for RecordNum2. I know this is because of the relationship of
Event.RecordNum = EventHistory.RecordNum

If there are no records in the EventHistory table, how can I still have ecordNum2 returned in the query with 0 showing as the count?

select Event.EventName,Event.EventType,COUNT(*) from event
INNER JOIN EventHistory WITH (NOLOCK) ON Event.RecordNum = EventHistory.RecordNum
WHERE Event.ClientCode = 223345 AND EventHistory.Vendor is not null
group by Event.EventName, Event.EventType

 

[PHV]

I'd try a LEFT join instead of an INNER one.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jasonhuibers]

1) Using the LEFT JOIN the correct records are returned, but the count is not correct for any records where there is no data in the EventHistory table.

2) I would also like to have the count based on the count of records in the eventhistory table where the Vendor column in the EventHistory table is not null

select Event.EventName,Event.EventType,
COUNT(*) as Replies
from event
LEFT JOIN EventHistory WITH (NOLOCK) ON Event.RecordNum = EventHistory.RecordNum
WHERE Event.ClientCode = 223345 --AND EventHistory.Vendor is null
group by Event.EventName, Event.EventType

 

[PHV]

SELECT Event.EventName, Event.EventType, COUNT(Event.RecordNum) AS Replies, COUNT(EventHistory.Vendor) AS yourAliasHere
FROM ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JarlH]

Untested:
[tt]select EventName,
EventType,
SUM(cnt)
FROM (select EventEvent.EventName as EventName,
Event.EventType as EventType,
(select count(*) from EventHistory
where Event.RecordNum = EventHistory.RecordNum
and EventHistory.Vendor is null) as cnt
FROM Event
WHERE Event.ClientCode = 223345)
group by Event.EventName, Event.EventType[/tt]

 

[jasonhuibers]

Using your query I get Incorrect syntax near the keyword 'group'.

 

[JarlH]

Try and replace the derived table with a view. It may work.

 

[PHV]

Did you try my suggestion timestamped 4 Jun 10 3:12 ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jasonhuibers]

Yes PHV, your exp for Vendor did the trick - many thanks