• Introducing Dark Mode! Switch by clicking on the lightbulb icon next to Search or by clicking on Default style at the bottom left of the page!

How to solve this self joining problem?

Andrew2009

Well Known Member
Here's my fields and data in a business view

Name|Co-Worker|Related
John|Jane
Smith|Ken
Jane|John

In the Do Section of the UBE, I will look in Co-Worker column and if other records have the same value under the Name column then I'll put a letter X in the Related column for that record.

For example, given the above data set, the end result is below. John and Jane are related because John has Jane under his Co-Worker column and vice versa, Jane has John under her Co-Worker column. So John and Jane have X in the Related column. Smith does not have any records that have Ken so Smith does not have letter X

Name|Co-Worker|Related
John|Jane|X
Smith|Ken
Jane|John|X

Any ideas on how to do this?

Thanks
 

RussellCodlin

Reputable Poster
Simplest way would be with a work table. Depending on the size of the record set you can either loop through the previously written records to find a match or alternatively do a select in each iteration to check the source table for a match.

Once you've built the table you can then run a conditional section to generate the report output from the work table. Make sure the work table has a key identifier that is unique to the UBE job so that you only deal with your records (not another batch job's) and also can clean up the data after the report has generated it's output.
 

BBritain

VIP Member
Hi Andrew2009,

I think that I would use a database view (http://www.jdelist.com/vb4/showthre...-Business-View-Limit?highlight=virtual+tables) where the table is linked to itself.

pseudo-code SQl
SELECT A.Name, A.Co-Worker {and other fields here} WHERE A.Name = B.Co-Worker AND A.Co-Worker = B.Name

I have done this before with F0911 is linked to itself but LNID = LNID+1 so I know it has worked in the past.

If the link doesn't explain enough the search on 'Virtual Table', and that will pull up more.

Let us know how it works out for you.

Ben again,
 
Top