Help need for link problem

[fsreport]

Hi Folks

cr 9.0
ms sql

i have this report and need to show all contact that does not match the the rep_alias table

in ms sql query designer i used this
***************************************
select contact_id

from contact

where contact_id not in (select contact_id from rep_alias ra, firm f where ra.firm_id = f.firm_id and channel = 'NBD')
********************************
returns 267294 records

now my report i have 3 tables

contact---------------rep_alias------------firm
contact_id>>>>>>contact_id
**************firm_id>>>>>>>>firm_id
***************************channel

the > means my link to table are base on these field.

in my record selection of the report i have this

not(contact.{contact_id} in [rep_alias.contact_id}])
and
firm.channel = 'NBD'

NOTE the tables are link to =

result 0 records return
i baffle why no record return??????? :-(
Can someone plz help me

thanks

fsreport

 

[IanWaterman]

Its your links

you will need to make rep_alias your lead table, keep your equal join to firm, then link from rep_alias to contact with a left outer join.

Change your filter to

isnull((contact.{contact_id})
and
firm.channel = 'NBD'

Ian

 

[fsreport]

hi
thanks but i tried
what you recommend
and no records
isnull((contact.{contact_id})
and
firm.channel = 'NBD'

any other option plz



fsreport

 

[IanWaterman]

Have you definitely made the join from from rep_alias to contact a left outer join.

Your links should now look like

rep_alias Contact
contact_id----left outer-------------ContactId
firm_id>>>equal>>>>>firm_id
********************channel


Ian

 

[fsreport]

heloo
Yes the links are as you mention but sill nothing


fsreport

 

[fsreport]

here is what it shows when i ask to see sql expression

SELECT "FIRM"."C_NAME", "FIRM"."CHANNEL",
"CONTACT"."CONTACT_ID", "CONTACT"."C_NAME"
FROM "FundSpace_LM"."dbo"."CONTACT" "CONTACT" INNER JOIN "FundSpace_LM"."dbo"."FIRM" "FIRM" ON "CONTACT"."FIRM_ID"="FIRM"."FIRM_ID"
WHERE "CONTACT"."CONTACT_ID" IS NULL AND "FIRM"."CHANNEL"='NBD'
ORDER BY "FIRM"."CHANNEL", "CONTACT"."CONTACT_ID"

i dont see the left outer join to the rep-alias table?????????

fsreport

 

[fsreport]

more info
when i look at the database expert and check the link
REP_ALIAS.CONTACT_ID ->CONTACT.CONTACT_ID

joint type
.Left Outer Join is selected

Link Type

. = is selected

fsreport

 

[fsreport]

more info
i made some minor changes to the report
and have this showing in the Show SQL query
SELECT
"FIRM"."C_NAME",
"FIRM"."CHANNEL",
"CONTACT"."CONTACT_ID",
"CONTACT"."C_NAME",
"REP_ALIAS"."CONTACT_ID"
FROM
("FundSpace_LM"."dbo"."REP_ALIAS" "REP_ALIAS"
LEFT OUTER JOIN "FundSpace_LM"."dbo"."CONTACT" "CONTACT" ON "REP_ALIAS"."CONTACT_ID"="CONTACT"."CONTACT_ID")
INNER JOIN "FundSpace_LM"."dbo"."FIRM" "FIRM" ON "CONTACT"."FIRM_ID"="FIRM"."FIRM_ID"
WHERE ("CONTACT"."CONTACT_ID" IS NULL OR "CONTACT"."CONTACT_ID"='') AND "FIRM"."CHANNEL"='NBD'
ORDER BY "FIRM"."CHANNEL", "CONTACT"."CONTACT_ID"

still nothing

fsreport

 

[fsreport]

Hi
I got figure out
made right outer join
and change to isnull(Rep_Alias.contact_id)

fsreport