January 11, 2010 at 3:21 am
My latest challenge is trying to join three tables. This should be a simple task but for some reason it has become a stumbling block to my data driven web application. This is what I have:
Table 1 is a lookup table and has a primary key.
Table 2 has a primary key (self incrementing ID) and a foreign key with Table 1 and with Table 3
Table 3 has a primary key, a foreign key relationship with Table 2, and a majority of the data that currently displays in the web application. There is no relationship between Table 1 and Table 3.
Let me try to explain the concept of how this should work. Table 2 and Table 3 records will be displayed in the web application with the user selecting one of the values from Table 2. This will then be sent back to Table 2 in an update statement.
Currently, Table 2 is completely empty since this is a new application but I have managed to either get records from Table 3 to display and NULL values for Table 2 (it shouldn't be NULL, it should show the look up value from Table 1), or I can get the "lookup" value to show but then the records from Table 3 are NULL.
I have tried right and left outer joins, I have tried full outer joins, and I have tried nested select statements all come back with the same result.
The code looks something like this:
Select
tTT.TvlID,
tTT.TvlName,
tTT.TvlLocation,
tTT.TvlDate,
tCA.BranchID, --this is currently empty until a user selects it via the web app
tCA.DivisionID, --this is currently empty until a user selects it via the web app
lkup.Organization --This is what is displayed in the web application so the
--user can select the appropriate value to update Table2
From
(Table2 tCA right outer join Table1 lkup on tCA.lkupFKID = lkup.ID)
left outer join Table3 tTT on tTT.FKID = tCA.tTTFKID
The above gives me the lkup.Organization value and correctly reflects the NULL values in Table 2 but I don't get the records from Table3, it displays NULL.
If I switch the joins around:
Select
tTT.TvlID,
tTT.TvlName,
tTT.TvlLocation,
tTT.TvlDate,
tCA.BranchID, --this is currently empty until a user selects it via the web app
tCA.DivisionID, --this is currently empty until a user selects it via the web app
lkup.Organization --This is what is displayed in the web application so the
--user can select the appropriate value to update Table2
From
(Table2 tCA left outer join Table1 lkup on tCA.lkupFKID = lkup.ID)
right outer join Table3 tTT on tTT.FKID = tCA.tTTFKID
I get the records from Table3 but I don't get the Table1 lkup.Organization value.
FULL OUTER Joins don't work either, so I'm left wondering do I need to do a UNION?
I appreciate any thoughts, I've scoured the forums looking for someone who is trying to do the same thing I am but I haven't had any luck. If someone can point me to an example in BOL or out at Microsoft, I'd greatly appreciate it!
January 11, 2010 at 4:44 am
You have a circular reference !!!!
Table2 has a FK to Table3
AND
Table3 has a FK to Table2
- What's the point ??
- are the FK columns Nullable ?
Avoid circular references !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 11, 2010 at 4:59 am
Hi ALZDBA,
Thanks for the comment, but I'm not sure I understand why you think it's circular.
Table 2 has a Foreign Key with Table 1 (Table 1 is the lookup table)
Table 2 has a Foreign Key with Table 3 (Table 3 holds a majority of the data)
Table 2 is updated from the web app based on end user selection of the value displayed (and this comes from Table 1)
How is that circular?
No the FK Columns in Table 2 are not NULLABLE.
Thanks again for your input.
January 11, 2010 at 5:13 am
mullins.tammy (1/11/2010)
There is no relationship between Table 1 and Table 3.Currently, Table 2 is completely empty
If you are joining table 1 to table 3 via table 2, you will only get values for both table1 and table3 in the same output when there are matching values in table2 i.e. when there's at least one row in table2 which has a fk both for table1 and for table3.
With no values at all in table2, you cannot join table1 and table3.
You state that there are no values in table2 because it's a new app: can you make up some test data?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply