Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Issue with JOIN relationship

Status
Not open for further replies.
jasonhuibers

jasonhuibers

Programmer
Sep 12, 2005
290
CA
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
 
Sort by date Sort by votes
I'd try a LEFT join instead of an INNER one.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
SELECT Event.EventName, Event.EventType, COUNT(Event.RecordNum) AS Replies, COUNT(EventHistory.Vendor) AS yourAliasHere
FROM ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
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]
 
Upvote 0 Downvote
Using your query I get Incorrect syntax near the keyword 'group'.
 
Upvote 0 Downvote
Try and replace the derived table with a view. It may work.
 
Upvote 0 Downvote
Did you try my suggestion timestamped 4 Jun 10 3:12 ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Yes PHV, your exp for Vendor did the trick - many thanks
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

BobThornton
  • Locked
  • Question
Cross Join in an Insert Into
Replies
1
Views
380
carp
carp
mmiah1982
  • Locked
  • Question
Extracting mutiple data items from DB with JSON string format column
Replies
2
Views
364
mmiah1982
mmiah1982
fredong
  • Locked
  • Question
Update upper row data with next row data that was detached partially from import.
Replies
6
Views
546
carp
carp
mikrom
  • Locked
  • Question
from a table with symmetric relation select every pair only once
Replies
0
Views
290
mikrom
mikrom
manbob
  • Locked
  • Question
need a help with sub query getting group ereor, please correct
Replies
2
Views
412
carp
carp

Part and Inventory Search

Sponsor

Back
Top