SQLTeam.com | Weblogs | Forums

Give is not producing correct results



Greetings experts,

when I run the following query, it correctly gives me all the records from tableA but all NULL values from tableB even though there are records in tableB.

Any ideas what I am doing wrong?

SELECT DISTINCT isnull(a.district_combo,'Not available'),
  COALESCE(a.str_number,'')+' '+COALESCE(a.str_name,'')+' '+COALESCE(a.str_suffix,'')+' '+COALESCE(a.str_apt,'') AS res_addr,
  COALESCE(a.mail_str_num,'')+' '+COALESCE(a.mail_str_name,'')+' '+COALESCE(a.mail_srt_suff,'')+' '+COALESCE(a.mail_apt,'')+' '+COALESCE(a.mail_city,'')+' '+COALESCE(a.mail_state,'')+' '+COALESCE(a.mail_zip,'') AS mail_address,
  b.* FROM dbo.tableA a LEFT OUTER JOIN dbo.tableB b ON a.record_id=b.record_id


Are you certain that there are matches between a.record_id and b.record_id? This query may shed some light on how many matches there are

SELECT COUNT(*) as TotalRows,
       COUNT(a.record_id) AS [A ID],
       COUNT(DISTINCT a.record_id) AS [Dis A ID],
       COUNT(b.record_id) AS [B ID],
       COUNT(DISTINCT b.record_id) AS [Dis B ID]
FROM dbo.tableA AS a
    LEFT OUTER JOIN dbo.tableB AS b
        ON a.record_id=b.record_id


Hi Kristen.

This is what I get so far:

TotalRows	A ID	        Dis A ID	B ID	Dis B ID
580062	        580062	          580062	0	0

In the original query I posted, I filtered with one specific record that exists on both tables but it didn't display the records from tableB


How is record_id defined in both tables (what is the data type)? Could one table have an unprintable character?