An outer join with a view that has an inner join

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

An outer join with a view that has an inner join

Author

Topic

bmsgharris
Starting Member

10 Posts

Posted - 2006-03-21 : 03:54:58

Hi

I have a query that needs to perform an outer join on two tables that have an inner join that are in a view. I.e.

CREATE VIEW TEST AS
SELECT
t1.col1, t2.col2
FROM
table1 t1, table2 t2
WHERE
t2.col1 = t1.col1

SELECT * FROM table3 t3, test t WHERE t.col2 =* t3.t2id

When I run the query I get the following error:

Msg 303, Level 16, State 1, Line 1
The table 'table2' is an inner member of an outer-join clause. This is not allowed if the table also participates in a regular join clause.

Now my understanding of a view is that it is like a table but built from the underlying select statement, i.e. the SQL Parser treats the view as a table, and not insert the view in the SQL Statement. Also running Profiler I get the exact same query being run. This is because I want to avoid using a temporary table.

I am using SQL Server 2000 SP4

TIA

Graham Harris

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-03-21 : 06:24:05

I'd get into the habit of using ANSI syntax for joins. It may well be that this solves your problem, as I've never had a similar problem before. In addition, you could replace the view with either a derived table or a nested join depending on the amount of data you're dealing with, etc.:


-- derived table
SELECT     t1t2.col1, 
           t1t2.col2, 
           t3.*
FROM       table3 AS t3
LEFT JOIN  (
             SELECT    t1.col1, t2.col2
             FROM      table1 AS t1
             JOIN      table2 AS t2
             ON        t1.col1 = t2.col1
            ) AS t1t2
ON          t3.t2id = t1t2.col2

--nested join
SELECT     t1.col1, 
           t2.col2, 
           t3.*
FROM       table3 AS t3
LEFT JOIN  table1 AS t1
JOIN       table2 AS t2
ON         t1.col1 = t2.col1
ON         t3.t2id = t2.col2

Mark

bmsgharris
Starting Member

10 Posts

Posted - 2006-03-21 : 06:34:13

The trouble is that I find that using inner/outer join syntax as I find it confusing!!! Is there any decent resource that can explain the syntax etc. clearly? The only reason I ask is that I learnt my SQL in Oracle and Oracle, at the time, did not support the inner/outer join syntax.

Thanks

Graham Harris

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-03-21 : 08:21:58

Not sure about online resources (other than Books Online), but any good SQL book will cover this syntax in some detail. It's a pretty straightforward process to move from one to the other. Your WHERE clause becomes the ON part of the join. and instead of '*' you specify the join as being INNER or LEFT OUTER (most people tend to standardise on LEFT OUTER joins and don't use RIGHT OUTER joins). NB you can do without the INNER and OUTER keywords and just use JOIN and LEFT. So:

SELECT *
FROM a, b
WHERE a.id = b.id

becomes

SELECT *
FROM a
JOIN b
ON a.id = b.id

SELECT *
FROM a,b
WHERE a.id = * b.id

becomes

SELECT *
FROM b
LEFT JOIN a
ON b.id = a.id

If you have no constraint in the where, that becomes a CROSS JOIN, i.e.:

SELECT *
FROM a, b

becomes

SELECT *
FROM a
CROSS JOIN b

Not sure how to do a FULL JOIN in the old syntax, and I can't experiment as I'm using SQL Server 2005 and it's not supported without changing compatibility settings (another good reason to stop using it!)

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources