×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Issue with JOIN relationship

thread220-1606609

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

RE: Issue with JOIN relationship

(OP)
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
 

RE: Issue with JOIN relationship

Untested:
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

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

RE: Issue with JOIN relationship

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

RE: Issue with JOIN relationship

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close