| Author |
Topic |
|
nicki_assumption
Yak Posting Veteran
69 Posts |
 Posted - 2005-03-16 : 15:28:13
|
| Hello- I'm trying to perform a full outer join on 3 tables. One, two or all of the tables may or may not have the column PrID in common. I want to end up with one row per PrID with results from all 3, whether it's there or not (NULL is fine). Even though each of the tables has a unique number of rows per PrID, I am ending up with about 6 dupes in the results. I think I am joining wrong, here is the sql, I'd appreciate any insight/opinions:select ISNULL(ISNULL(a.LV_PrID,b.LN_PrID),c.SR_PrID) as comp_prid, * from tblCompLV_cross a full outer join tblCompLN_cross b ona.LV_PrID=b.LN_PrIDfull outer join tblCompSR_cross c on b.LN_prid=c.SR_prid ----So that I can get a count, I put all of it into a view, this is how I know I have dupes:drop view complications create view complications as select ISNULL(ISNULL(a.LV_PrID,b.LN_PrID),c.SR_PrID) as comp_prid, * from tblCompLV_cross a full outer join tblCompLN_cross b ona.LV_PrID=b.LN_PrIDfull outer join tblCompSR_cross c on b.LN_prid=c.SR_prid ---this reveals dupes:select count(comp_prid), comp_prid from complicationsgroup by comp_pridhaving count(comp_prid)>1Thanks alot.Nicki |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-03-16 : 15:31:15
|
| 1st. What do you mean ny "dupes"2nd. We'll need to see the DDL of the tables3rd. Sample Data from the source (Preferable in DML Statements), and the results would be helpful.Post that, and you'll get an answer in minutes.Brett8-) |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-03-16 : 15:43:24
|
| I have no idea what you are trying to do based on your description, but I assure you there is no need for FULL OUTER JOINS ... give us some more info (the stuff Brett mentions) and we can help you out. Instead of showing us what you are trying and saying it doesn't work, explain to us what you are trying to accomplish.- Jeff |
|
|
|
nicki_assumption
Yak Posting Veteran
69 Posts |
Posted - 2005-03-16 : 16:05:51
|
| Hi- I'll try explaining first ...I have 3 tables- all 3 tables contain information about processes which are identified by prid- that's the id of the process. The prid is unique in all those tables- distinct in sql server world. No duplicates (my short word was dupes) in any of the tables. Between the tables, they share some processes- prids. The three tables can share a prid with one or more of the other tables. So, a prid may show up in up to 3 tables. I need to extract the info in all tables whether the prid is only in one table or in all tables but I only want one row per prid. The reason why i'm selecting the isnull is so I have one column I can join to other tables later that has the prid. It's probably just complicating things here though, I should get rid of it.Is that enough info? If I had only two tables, it's the perfect candidate for a Full Outer Join but somehow with 3, the results are including duplicate entries for prids. It works perfectly with just 2 actually- I could create the view out of that and join it to the other afterwards but I'd really like to know the right way to do this.thanks so much for your thoughts.nicki |
|
|
|
nicki_assumption
Yak Posting Veteran
69 Posts |
Posted - 2005-03-16 : 16:16:27
|
| i created a diagram - i'm not sure how to post it though. i could send a screen shot? it's only 3 tables though with only one thing in common- the join is on LV_PrID,LN_PrID, and SR_PrID. i forgot to also explain that prid has been renamed b/c i needed to create a view which needs unique column names so that is probably confusing everything too. sorry about that- the join for the prid is from lv_prid to ln_prid to sr_prid. thanks so much |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-03-16 : 16:17:55
|
quote:
Originally posted by X002548
1st. What do you mean ny "dupes"2nd. We'll need to see the DDL of the tables3rd. Sample Data from the source (Preferable in DML Statements), and the results would be helpful.Post that, and you'll get an answer in minutes.Brett8-)
I don't like to read....do you think you can supply what we asked for?Brett8-) |
|
|
|
nicki_assumption
Yak Posting Veteran
69 Posts |
Posted - 2005-03-16 : 16:29:23
|
| i know- that's why i tried to be pithy the first time.unfortunately i can't supply you with data b/c i'm bound by a hipaa contract- it's a medical db, but i can send you a screen shot of my 3 tables if that would help. dupes is short for duplicates. maybe our friend who asked for an explanation likes to read though ;) |
|
|
|
MattieBala69
Starting Member
30 Posts |
Posted - 2005-03-16 : 16:33:58
|
| Hey, I could be wrong but if u do GROUP BY that should eleminate the duplicates. If thats what u are looking for |
|
|
|
nicki_assumption
Yak Posting Veteran
69 Posts |
Posted - 2005-03-16 : 16:36:17
|
| interesting- i should try that. i would basically have to sum up all the columns or something. but there are hundreds of columns, that could be a nightmare. something about the join i think is incorrect though for it to produce duplicates. it doesn't make sense if all 3 have no dupes and i'm doing a full outer on them, where do the duplicates come from? a quagmire. |
|
|
|
nicki_assumption
Yak Posting Veteran
69 Posts |
Posted - 2005-03-17 : 11:04:18
|
| If anyone is still reading this- i found the solution. I actually needed to create another table. This gets me unique rows on three tables that overlap some. Full outer joins are not possible on 3 tables apparently.Select dtAllPrids.comp_prid, a.*, b.*, c.*From( Select LV_PrID As comp_prid From tblCompLV_cross Union Select LN_PrID As comp_prid From tblCompLN_cross Union Select SR_PrID As comp_prid From tblCompSR_cross) dtAllPridsLeft Join tblCompLV_cross As a On ( ... )Left Join tblCompLN_cross As b On ( ... )Left Join tblCompSR_cross As c On ( ... ) |
|
|
|
PW
Yak Posting Veteran
95 Posts |
Posted - 2005-03-17 : 11:14:44
|
| You're welcome. |
|
|
|
|
full outer join on 3 tables