full outer join on 3 tables
-
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 on
a.LV_PrID=b.LN_PrID
full 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 on
a.LV_PrID=b.LN_PrID
full outer join tblCompSR_cross c on b.LN_prid=c.SR_prid
---this reveals dupes:
select count(comp_prid), comp_prid from complications
group by comp_prid
having count(comp_prid)>1
Thanks alot.
Nicki
-
Yep,
Full outer is appropriate for 2 tables, Three or more you have to go the Union Route:
select IDS.Id as comp_prid, ....
from
(select LV_PrID as Id tblCompLV_cross
union
select LN_PrID tblCompLN_cross
union
Select SR_PrID tblCompSR_cross ) IDs
Left join LV_PrID tblCompLV_cross a
on IDS.Id = a.LV_PrID
Left Join LN_PrID tblCompLN_cross b
on IDS.Id = b.LN_PrID
Left Join SR_PrID tblCompSR_cross c
on IDS.Id = c.SR_PrID
You are probably getting into problems because of the name you choosed for the view
Btw it is not good to use "*" on a view
HTH
* Noel -
Hmm, I've never been able to select from a query in ms sql server- that's only one of the many errors this gives me
I thought about using union but they have no columns in common. Unfortunately I can't get this to run enough to comment further. The "*" is because there are hundreds of columns- it's a warehouse type situation. I'll keep cutting it apart and trying but i'm almost positive about selecting from a result set of a query not being an option. I love that feature of other databases though- if I can use, it will make my life much better 
-
oh- just realized i never specified ms sql server. sorry.
-
I don't get what you are trying to say but...I was joking about a view called complications
What is the problem with the query? And even though you have a massive amount of fields you should specify them in case they have the same name on different tables!
if you want a quick test you can change the select list to:
select IDS.Id as comp_prid, a.*,b.*,c.*
from
...
* Noel -
Hi- I'll explain further...
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. I renamed prid to be sl_prid, lv_prid and ln_prid b/c the view needs unique column names. That's where my join is though- on those 3 columns. The isnull function is used so i have one column with no nulls in it of prids to join to other 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. If you think of it as 3 circles which overlap each other, i think it's a common math problem. They overlap all three, and each can overlap one other and not the other
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
-
So, really, you need to join four tables, 1 which gives you the entire set of possible prids, left joined to the other 3. You construct the 4th table as a derived table which is a UNION of the prids in the other 3:
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
) dtAllPrids
Left Join tblCompLV_cross As a On ( ... )
Left Join tblCompLN_cross As b On ( ... )
Left Join tblCompSR_cross As c On ( ... )
-
except that selecting from select statements is not legal in sql server. unless i've been wrong all these years and they've changed something.
select * from (select * from tblCompSR_cross)
for example, will not work. in oracle and mysql, and others it will, but not ms sql server. unless you're sure it does and i'm doing something wrong. are you sure that should work? the subselect? i woudl love that
-
I am sorry to disappoint you but
select * from (select * from tblCompSR_cross) q
WORKS!
* Noel -
not on my server. what version do you use? i'm talking about microsoft sql server. i'm on 7. are you really able to do this? i can't get around the error. incorrect syntax.
-
you can however use a nested subquery in a where statement on sql server but not the from. i would love to be proven wrong.
-
quoted from this article
http://www.aspfree.com/c/a/MS-SQL-Server/Subqueries-and-Query-Expressions/6/
"Conceptually, SQL should let you substitute the view definition right into the query, like this:
SELECT MFR, PRODUCT, SUM (QTY)
FROM (SELECT * FROM ORDERS WHERE AMOUNT > 10000)
GROUP BY MFR, PRODUCT
But the SQL1 standard doesn’t allow a subquery in this position in the WHERE clause..."
How I wish- this is a great feature of other databases. Is there some other way to write your query without it. Maybe I need to combine a bunch of left outer joins.
-
>> i can't get around the error. incorrect syntax.
What's the error you get ? What's your DB compatibility level set at ? What's the exact SQL you're running that generates the error (since I posted incomplete SQL with the joins omitted, maybe your syntax error is elsewhere and not in the derived table).
-
i'm just testing the nested query
select * from (select * from anytable)
if you are going to test it- use any table. run it on dual
as far as compatability goes- it's microsoft, it's not compatable with anything. i'm telling you, for years now i've never been able to do that. if you are now allowed to use that syntax in microsoft, it's a very new feature.
the error is incorrect syntax.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
-
ah, you have to name the nested query. it does work. this has to be a new feature. it's been a few years since i've used ms sql. in oracle and most others, you don't need to name. ok, now i will try the other queries.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply