Join type based on condition?

Given two views, I need to join them based on the number of rows
returned as follows:
ViewA 1+ rows, ViewB 0 rows: ViewA LEFT JOIN ViewB
ViewA 0 rows, ViewB 1+ rows: ViewA RIGHT JOIN ViewB
ViewA 1+ rows, ViewB 1+ rows: ViewA INNER JOIN ViewB
Easy enough to implement in a stored proc with a conditional statement,
but I am looking for syntax that will produce the joins in a view (for
reporting processes that do not support SP execution).Can you give some DDL, sample data, and desired results?
Sounds like a FULL OUTER JOIN to me, we just have to understand what data
you want presented in each condition.
See http://www.aspfaq.com/5006 for help on asking more effective questions.
"Matt" <bsg075@.gmail.com> wrote in message
news:1123614023.861327.180310@.g14g2000cwa.googlegroups.com...
> Given two views, I need to join them based on the number of rows
> returned as follows:
> ViewA 1+ rows, ViewB 0 rows: ViewA LEFT JOIN ViewB
> ViewA 0 rows, ViewB 1+ rows: ViewA RIGHT JOIN ViewB
> ViewA 1+ rows, ViewB 1+ rows: ViewA INNER JOIN ViewB
> Easy enough to implement in a stored proc with a conditional statement,
> but I am looking for syntax that will produce the joins in a view (for
> reporting processes that do not support SP execution).
>|||A Full Outer Join would work when one of the two views returns no
records, but an Inner Join is needed when they both return rows.
Using tables in place of views for an example:
CREATE TABLE tblA (uid INT, orgid INT)
CREATE TABLE tblB (uid INT, orgid INT)
I need to implement the following in a view:
IF SELECT(COUNT(*) FROM tblA)=0 OR SELECT(COUNT(*) FROM tblB)=0
SELECT orgid FROM tblA FULL JOIN tblB on tblA.uid = tblB.uid
ELSE
SELECT orgid FROM tblA INNER JOIN tblB on tblA.uid = tblB.uid|||just a stab in the dark:
--ViewA 1+ rows, ViewB 0 rows: ViewA LEFT JOIN ViewB
select * from <ViewA LEFT JOIN ViewB >
where
--ViewA 1+ rows
exists(select 1 from viewA)
-- ViewB 0 rows:
and not exists(select 1 from viewB)
union all
--ViewA 0 rows, ViewB 1+ rows: ViewA RIGHT JOIN ViewB
select * from <ViewA RIGHT JOIN ViewB >
where
not exists(select 1 from viewA)
and exists(select 1 from viewB)
union all
--ViewA 1+ rows, ViewB 1+ rows: ViewA INNER JOIN ViewB
select * from <ViewA INNER JOIN ViewB >
where
exists(select 1 from viewA)
and exists(select 1 from viewB)|||Try this one:
SELECT tblA.uid, tblA.orgid, tblB.uid, tblB.orgid
FROM tblA
JOIN tblB
ON tblA.uid = tblB.uid
UNION ALL
SELECT tblA.uid, tblA.orgid, tblB.uid, tblB.orgid
FROM tblA
FULL JOIN tblB
ON 1=1
WHERE NOT EXISTS
(SELECT *
FROM tblA)
OR NOT EXISTS
(SELECT *
FROM tblB) ;
David Portas
SQL Server MVP
--|||That will work. Thanks!|||Here's a variation on David's solution that avoids UNION. I
don't know that it will run as fast, though:
select *
from T
full outer join U
on 1=1
where (T.i = U.i)
or not exists (
select * from T
) or not exists (
select * from U
)
Steve Kass
Drew University
"Matt" <bsg075@.gmail.com> wrote in message
news:1123617379.421948.237990@.g47g2000cwa.googlegroups.com...
> That will work. Thanks!
>