Writing a report drawing information from a SQL Server 2005 table
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?
Software/Hardware used:
Software/Hardware used:
ASKED:
December 8, 2008 7:25 PM
UPDATED:
December 8, 2008 9:22 PM
Answer Wiki:
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.
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
