Help with query logic, please

Question

Help with query logic, please

Tim Fitzgerald

Valued Member

Points: 70
More actions
January 2, 2007 at 3:20 pm

#118491

Hi all,
I have two tables. The first one 'AccountActivity' and the other 'PaymentInfo'.
When a payment is made, the user marks the account with a status code which is saved in 'Account Activity' and they also save the payment information in 'PaymentInfo'; the two tables are related only by the 'AccountId'
What I would like to do is check the 'AccountActivity' table for status codes of say, 405 and 405 and see if they 'AccountId' exists in the 'PaymentInfo' table.
INNER JOIN will not suffice because I don't want to see matching records, I want to see un-match records.
This is simple but yet, I can't seem to achieve the correct results.
Thanks,
Tim

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

mrpolecat SSCertifiable Points: 7014 More actions · Answer 1

Not sure I understand because your request says you want to find records where the accounid exists in both tables but then you say you want unmatched records.

To get unmatched records use a left join and use where the right side accountid is null

John Rowan SSC Guru Points: 56440 More actions · Answer 2

Your description is a bit confusing Tim. Can you post the DDL for both tables, sample data, and an example of your desired results.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 3

I agree, more information would help us help you. But, I'm willing to take a stab at it. In the description you said status codes 405 and 405, but you might have meant 405 and 406 so the query will assume that's what you meant.

Select a.AccountID
from AccountActivity as a LEFT JOIN PaymentInfo as p
on a.AccountID = p.AccountID
where p.AccountID is null and a.StatusCode in (405,406)

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

Tim Fitzgerald Valued Member Points: 70 More actions · Answer 4

I apologize for the vague and incomplete information. The information, provided so far, may be exactly what I'm looking for. I'll try the suggested queries when I get to the office tomorrow.

Thanks!

Tim Fitzgerald Valued Member Points: 70 More actions · Answer 5

The above suggestions helped out a great deal. I am able to get the results I need.

Thanks!