Table join issue

[smibarb]

I have a simple report in which instrument serial numbers are listed if they meet a certain criteria. 501 numbers are listed from a much longer list. I then added a table which has results for these instruments linked by the serial number. I used a left outer join. It was my understanding that by using a left outer join, any meters without results would still be listed. I am only getting meters listed that have results. Any suggestions as to what I am doing wrong? CR ver 11.


SELECT "bu_t"."bu_sys_id", "bu_t"."bu_serial", "bu_t"."cfg_stale_flg", "bu_t"."not_cfg_flg", "bu_t"."deleted_flg", "rslt_qc_t"."rslt_type_id"
FROM "lfs_main"."dbo"."rslt_qc_t" "rslt_qc_t" LEFT OUTER JOIN "lfs_main"."dbo"."bu_t" "bu_t" ON "rslt_qc_t"."bu_sys_id"="bu_t"."bu_sys_id"
WHERE "bu_t"."deleted_flg"<>1
ORDER BY "bu_t"."bu_serial"

 

[synapsevampire]

I assume that you're using a Command Object and pasting in this SQL.

It appears that you have overiding criteria in your WHERE clause:

"bu_t"."deleted_flg"<>1

This states ONLY those rows.

Try:

SELECT "bu_t"."bu_sys_id", "bu_t"."bu_serial", "bu_t"."cfg_stale_flg", "bu_t"."not_cfg_flg", "bu_t"."deleted_flg", "rslt_qc_t"."rslt_type_id"
FROM "lfs_main"."dbo"."rslt_qc_t" "rslt_qc_t" LEFT OUTER JOIN "lfs_main"."dbo"."bu_t" "bu_t" ON "rslt_qc_t"."bu_sys_id"="bu_t"."bu_sys_id"
WHERE "bu_t"."deleted_flg" is null
or
"bu_t"."deleted_flg"<>1
ORDER BY "bu_t"."bu_serial"

Please remember to post your database/connectivity when asking about SQL...

btw, this isn't a Crystal question, it's a basic SQL question as it works the same for all databases that I know of.

-k