Issue with JOIN relationship
Issue with JOIN relationship
(OP)
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
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
RE: Issue with JOIN relationship
Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: Issue with JOIN relationship
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
RE: Issue with JOIN relationship
FROM ...
Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: Issue with JOIN relationship
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
RE: Issue with JOIN relationship
RE: Issue with JOIN relationship
RE: Issue with JOIN relationship
Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: Issue with JOIN relationship