Writing a report drawing information from a SQL Server 2005 table

Tags:
SQL Server 2005
SQL Server tables
I am writing a simple report that displays records from our customer table, a table that has no corresponding records in our contacts table. My code entered in SQL Server 2005 is as follows: select contractor.name,acctstatus, statusDetail, sales_person, loggedInBy,loginDate, lastUpdateBy, LastUpdateOn from contractor where contractor.name NOT IN (Select company_name from leads_contacts) I keep getting an empty result set, but I know we have companies that have no assigned contacts. Could you suggest a method to accomplish this?
ASKED: December 8, 2008  7:25 PM
UPDATED: December 8, 2008  9:22 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

That query should work.

You could accomplish the same using an outer join.

Something like this:
<pre>
SELECT c.name,acctstatus, c.statusDetail, c.sales_person, c.loggedInBy,c.loginDate, c.lastUpdateBy, c.LastUpdateOn
FROM contractor c LEFT OUTER JOIN leads_contacts lc
ON c.name = lc.company_name
WHERE lc.company_name IS NULL</pre>

… But if the problem is in the data, this will return an empty result set as well.

Do you know of some specific company that has no associated record in the leads_contacts table ? If so, I would run a query on leads_contacts for that specific company_name. If no row is returned, then your report should display at least that company.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following