Jump to content
  • 0

Self-Join Left-Outer



I've found an issue doing a left-outer self-join.  I can't get any criteria to work for what should be a simple left join; does anyone have any ideas?


One table, relevant columns are:

CID (Number) - ID number to join with other tables (other tables not relevant in this instance, relationship has not been defined in Caspio)
VID (Text[255]) - Name of the Vendor used
EndDate (Date/Time) - Date that the relationship with the Vendor ended

Let's have 3 people:
John Doe - CID 1
Billy Bob - CID 2
Fat Joe - CID 3

Table Enteries are as follows:
1, HR, *null*
1, Surefire, *null*
1, Vantage, *null*
2, HR, 01/01/2016
2, Surefire, 01/01/2016
3, HR, *null*
3, Vantage, *null*

Table should join to itself (left outer) with the CID as the relationship join. What I want to see is anyone with a *null* value for the HR end date and their corresponding Surefire information. View should populate as:

1, HR, *null*, 1, Surefire, *null*
3, HR, *null*, *null*, *null*, *null*

I can't get the criteria right to do this. When I restrict the criteria for the self-join to only join if the right table VID entry is "Surefire" it restricts all the results by that field. The view I end up with is:

1, HR, *null*, 1, Surefire *null*

Link to comment
Share on other sites

1 answer to this question

Recommended Posts

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...