Linking problem with two tables 1

[ynott]

How to see all business names?

Table 1
Business.ID
Business.Name

Table 2
Comment.ID
Comment.ReferenceID
Comment.Comment

Left outer join to Business.ID <-> Comment.ReferenceID

Now, if I list all Business.Name, I will see all Business names.

For the most part, every Business.Name will have a Comment.Comment. However, I need to see all of the Business.Name listings that dont' have a Comment.Comment. How can I do this?

 

[synapsevampire]

A left outer does that unless you've placed something in the selection formula to filter the comment table.

-k

 

[lbass]

In your record selection formula, use:

isnull({Comment.ReferenceID})

...if you want a list of names with no comments.

-LB

 

[ynott]

lbass, thanks for taking a shot, but that didn't work. I know it's probably something simple that's escaping me.

 

[lbass]

Please explain your results, instead of saying it didn't work. You do have the left join FROM the Business table TO the Comments table, right? And with no other selection criteria on the Comments table?

-LB

 

[ynott]

Sorry about that. Yes, left join with:

Left outer join to Business.ID <-> Comment.ReferenceID

No other selection criteria and no results.

isnull({Comment.ReferenceID}) is in the selection criteria

 

[lbass]

Left outer join FROM Business.ID TO Comment.ReferenceID?

Please go to file->report options and make sure that "Convert null values to default" is NOT checked. Or you could change the selection formula to:

isnull({Comment.ReferenceID}) or
trim({Comment.ReferenceID}) = ""

If there are any instances with no comments, this should work.

-LB

 

[ynott]

Convert null values did it. THANKS!

 

[synapsevampire]

Sorry, I misunderstood, I thought that you wanted all of them including the null ones, not ONLY the null ones.

-k