Conditional outer join help

I need to do an outer join in order to retrieve info in cases where some
table data does not exist, but doing this causes my query to return rows I
don't want for cases where data does exist in all tables. I have simplified
the problem as follows:
Say my data looks like this:
Table A Table B Table C
ID Code ID Code ID Code
1 11 11 A 11 X
1 12 12 A 12 Y
1 13 13 B 13 Z
2 21 21 C 21 X
My query needs to return data from other tables, which then join to Table A
on ID. I'm only interested in data from Table B and C where B.code = A and
C.code = Y. So effectively what I want returned from this part of the query
is
A.ID A.Code B.ID B.Code C.ID C.Code
1 12 12 A 12 Y
null null null null null null (where the null row is
from A.ID = 2)
The problem is, to get the null row returned from A.ID = 2, I have to do the
following:
select *
from A
left outer join B
on A.code = B.ID
and B.code = 'A'
left outer join C
on B.ID = C.ID
and C.code = 'Y'
But this means that I also get two extra rows returned for when A.ID = 1.
To get the correct rows returned for A.ID = 1, I need to do the following:
select *
from A
left outer join B
on A.code = B.ID
left outer join C
on B.ID = C.ID
where B.code = 'A'
and C.code = 'Y'
How can I do both?
Thanks in advance.Easy: replace the second outer join by an inner join between B and C. (I'm
not sure if you will have to put it into a subquery.)
Another possibility would be to put your first solution into a subquery
itself but this is a much less elegant solution.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
"janeNZ" <janeNZ@.discussions.microsoft.com> wrote in message
news:9E9A95BD-779A-4BCF-81A9-0FBE604FD45C@.microsoft.com...
>I need to do an outer join in order to retrieve info in cases where some
> table data does not exist, but doing this causes my query to return rows I
> don't want for cases where data does exist in all tables. I have
> simplified
> the problem as follows:
> Say my data looks like this:
> Table A Table B Table C
> ID Code ID Code ID Code
> 1 11 11 A 11 X
> 1 12 12 A 12 Y
> 1 13 13 B 13 Z
> 2 21 21 C 21 X
> My query needs to return data from other tables, which then join to Table
> A
> on ID. I'm only interested in data from Table B and C where B.code = A
> and
> C.code = Y. So effectively what I want returned from this part of the
> query
> is
> A.ID A.Code B.ID B.Code C.ID C.Code
> 1 12 12 A 12 Y
> null null null null null null (where the null row
> is
> from A.ID = 2)
> The problem is, to get the null row returned from A.ID = 2, I have to do
> the
> following:
> select *
> from A
> left outer join B
> on A.code = B.ID
> and B.code = 'A'
> left outer join C
> on B.ID = C.ID
> and C.code = 'Y'
> But this means that I also get two extra rows returned for when A.ID = 1.
> To get the correct rows returned for A.ID = 1, I need to do the following:
> select *
> from A
> left outer join B
> on A.code = B.ID
> left outer join C
> on B.ID = C.ID
> where B.code = 'A'
> and C.code = 'Y'
> How can I do both?
> Thanks in advance.|||Thanks Sylvain but inner joining between B and C eliminates the row of all
nulls that I need returned for A.ID = 2. I'm not sure how putting this in a
subquery would help? Can you be more specific?
Result after outer joining A and B on A.code = B.ID and B.code = 'A' is:
A.ID A.Code B.ID B.Code
1 11 11 A
1 12 12 A
null null null null (A.ID was 1)
null null null null (A.ID was 2)
So you can see that inner joining this to C on B.ID = C.ID is not going to
return the null rows. What am I missing in your explanation?
"Sylvain Lafontaine" wrote:

> Easy: replace the second outer join by an inner join between B and C. (I'
m
> not sure if you will have to put it into a subquery.)
> Another possibility would be to put your first solution into a subquery
> itself but this is a much less elegant solution.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
>
> "janeNZ" <janeNZ@.discussions.microsoft.com> wrote in message
> news:9E9A95BD-779A-4BCF-81A9-0FBE604FD45C@.microsoft.com...
>
>|||Sorry, but your repetition of the same names (ID and CODE) for differents
values may have mixed my little head. I've just took a little time to write
a test database with your data and here a query that I have made by
transforming your second query into a subquery and use it with an outer join
to the distinct values from A:
Select R.*, S.* from
(Select Distinct Id from A) as R Left outer join
(select A.Id as AId, A.Code as ACode, B.Id as BId, B.Code as BCode, C.Id as
CId, C.Code as CCode
from A left outer join B on A.code = B.ID
left outer join C on B.ID = C.ID
where (B.Code is Null and C.Code is Null)
or (B.code = 'A' and C.code = 'Y')
) as S
On R.Id = S.AId
and here are the result:
1 1 12 12 A 12 Y
2 NULL NULL NULL NULL NULL NULL
The first column is a new column that I have added and it simply gives the
list of distinct values for the ID of A. With the exception of this column,
this is exactly the result that you have asked for in your first post. I
have also added alias because of the multiple repetition of ID and CODE with
different meanings in the three tables.
Of course, we see that the two LEFT OUTER JOIN in the subqueries S are
useless and can be probably replaced with INNER JOIN to give the same
results but I'm not sure if this is the case for you because I don't know
enough about your real data for the rest of the tables.
There are probably other possibilities, too but now, it's getting to late
for me.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
"janeNZ" <janeNZ@.discussions.microsoft.com> wrote in message
news:3FBA7F59-9770-414D-8791-9B44631BCD33@.microsoft.com...
> Thanks Sylvain but inner joining between B and C eliminates the row of all
> nulls that I need returned for A.ID = 2. I'm not sure how putting this in
> a
> subquery would help? Can you be more specific?
> Result after outer joining A and B on A.code = B.ID and B.code = 'A' is:
> A.ID A.Code B.ID B.Code
> 1 11 11 A
> 1 12 12 A
> null null null null (A.ID was 1)
> null null null null (A.ID was 2)
> So you can see that inner joining this to C on B.ID = C.ID is not going to
> return the null rows. What am I missing in your explanation?
> "Sylvain Lafontaine" wrote:
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
Right now you have a magical "id" that does not tell us what it
identifies and a magical "code' that modeled as both strings and
numerics in the same schema. Then you have code and id are equi-joined
together. Here is my guess at what you might have meant to say:
CREATE TABLE Foobar
(foo_grp INTEGER NOT NULL,
member_id INTEGER NOT NULL PRIMARY KEY);
CREATE TABLE Foo
(member_id INTEGER NOT NULL PRIMARY KEY
REFERENCES Foobar(member_id),
foo_score CHAR(1) NOT NULL);
CREATE TABLE Bar
(member_id INTEGER NOT NULL PRIMARY KEY
REFERENCES Foobar(member_id),
bar_score CHAR(1) NOT NULL);
Table A on ID. I'm only interested in data from Table B and C where
B.code = A and C.code = Y. <<
SELECT foo_grp, member_id, 'A' AS bar_score, 'Y' AS foo_score
FROM Foobar
WHERE EXISTS
(SELECT *
FROM Foo AS F1, Bar AS B1
WHERE F1.member_id = Foobar.member_id
AND B1.member_id = Foobar.member_id
AND bar_score = 'A'
AND foo_score = 'Y');|||Hi Sylvain,
I really appreciate your help on this. Sorry that my attempt at
simplification has made things confusing. The real tables have hideous long
names and the referential integrity between them is full of holes.
I have tried to apply your solution but I don't understand how your inner
query generates the null column values. As you say, the outer joins in the
subqueries are effectively the same as inner joins. Therefore, there will b
e
no null column values in the S table. I tried adding the restrictions to th
e
inner joins (i.e. 'from A left outer join B on A.code = B.id and B.code = 'A
'
left outer join C on B.ID = C.ID and C.code = 'Y') but, of course, this mean
s
I will always get an extra row of nulls for the A.ID = 1 row. i.e. my resul
t
set would be (using your leading row of distinct values from A):
1 1 12 12 A 12 Y
1 NULL NULL NULL NULL NULL NULL
2 NULL NULL NULL NULL NULL NULL
Jane
"Sylvain Lafontaine" wrote:

> Sorry, but your repetition of the same names (ID and CODE) for differents
> values may have mixed my little head. I've just took a little time to wri
te
> a test database with your data and here a query that I have made by
> transforming your second query into a subquery and use it with an outer jo
in
> to the distinct values from A:
> Select R.*, S.* from
> (Select Distinct Id from A) as R Left outer join
> (select A.Id as AId, A.Code as ACode, B.Id as BId, B.Code as BCode, C.Id a
s
> CId, C.Code as CCode
> from A left outer join B on A.code = B.ID
> left outer join C on B.ID = C.ID
> where (B.Code is Null and C.Code is Null)
> or (B.code = 'A' and C.code = 'Y')
> ) as S
> On R.Id = S.AId
> and here are the result:
> 1 1 12 12 A 12 Y
> 2 NULL NULL NULL NULL NULL NULL
> The first column is a new column that I have added and it simply gives the
> list of distinct values for the ID of A. With the exception of this colum
n,
> this is exactly the result that you have asked for in your first post. I
> have also added alias because of the multiple repetition of ID and CODE wi
th
> different meanings in the three tables.
> Of course, we see that the two LEFT OUTER JOIN in the subqueries S are
> useless and can be probably replaced with INNER JOIN to give the same
> results but I'm not sure if this is the case for you because I don't know
> enough about your real data for the rest of the tables.
> There are probably other possibilities, too but now, it's getting to late
> for me.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
>
> "janeNZ" <janeNZ@.discussions.microsoft.com> wrote in message
> news:3FBA7F59-9770-414D-8791-9B44631BCD33@.microsoft.com...
>
>|||Hi Jane,
The null column values are not generated by the inner query but by the
Left Outer Join of the outer query.
Excerpt for the aliases, the big inner query is the same as your second
query in your first post and this query generate only one line. The other
inner query (the small one: (Select Distinct Id from A) ) generate only
two lines with two values: 1 and 2 and combined as a Left Outer Join to the
other inner query can give only two big lines: the first one with values
other than null and the other one with all null values.
Maybe you could post here the query that you have tried and that gives
three lines instead of two.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
"janeNZ" <janeNZ@.discussions.microsoft.com> wrote in message
news:71FC1170-6EA1-4F51-BB8F-A231E69513A0@.microsoft.com...
> Hi Sylvain,
> I really appreciate your help on this. Sorry that my attempt at
> simplification has made things confusing. The real tables have hideous
> long
> names and the referential integrity between them is full of holes.
> I have tried to apply your solution but I don't understand how your inner
> query generates the null column values. As you say, the outer joins in
> the
> subqueries are effectively the same as inner joins. Therefore, there will
> be
> no null column values in the S table. I tried adding the restrictions to
> the
> inner joins (i.e. 'from A left outer join B on A.code = B.id and B.code =
> 'A'
> left outer join C on B.ID = C.ID and C.code = 'Y') but, of course, this
> means
> I will always get an extra row of nulls for the A.ID = 1 row. i.e. my
> result
> set would be (using your leading row of distinct values from A):
> 1 1 12 12 A 12 Y
> 1 NULL NULL NULL NULL NULL NULL
> 2 NULL NULL NULL NULL NULL NULL
> Jane
> "Sylvain Lafontaine" wrote:
>|||Hi,
Okay I see what you mean. I have used a version of your solution and it
works although I have to repeat a large query. I feel like there should be
a
better way but I don't want to post the actual query. It's too large and th
e
relationships between the tables are too hard to see.
Thanks for your help.
jane
"Sylvain Lafontaine" wrote:

> Hi Jane,
> The null column values are not generated by the inner query but by the
> Left Outer Join of the outer query.
> Excerpt for the aliases, the big inner query is the same as your secon
d
> query in your first post and this query generate only one line. The other
> inner query (the small one: ? (Select Distinct Id from A) ? ) generate o
nly
> two lines with two values: 1 and 2 and combined as a Left Outer Join to th
e
> other inner query can give only two big lines: the first one with values
> other than null and the other one with all null values.
> Maybe you could post here the query that you have tried and that gives
> three lines instead of two.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
>
> "janeNZ" <janeNZ@.discussions.microsoft.com> wrote in message
> news:71FC1170-6EA1-4F51-BB8F-A231E69513A0@.microsoft.com...
>
>|||You can use a temporary table or a table variable to store the result of
this large query and avoid repeating it.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
"janeNZ" <janeNZ@.discussions.microsoft.com> wrote in message
news:CD3584C7-3B1A-4010-95DD-F1722B2800CC@.microsoft.com...
> Hi,
> Okay I see what you mean. I have used a version of your solution and it
> works although I have to repeat a large query. I feel like there should
> be a
> better way but I don't want to post the actual query. It's too large and
> the
> relationships between the tables are too hard to see.
> Thanks for your help.
> jane
>
> "Sylvain Lafontaine" wrote:
>|||And finally, if you query is really complex, then the use of other options
like Exists() could be your best solution.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:%23UcmOnIYFHA.796@.TK2MSFTNGP09.phx.gbl...
> You can use a temporary table or a table variable to store the result of
> this large query and avoid repeating it.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
>
> "janeNZ" <janeNZ@.discussions.microsoft.com> wrote in message
> news:CD3584C7-3B1A-4010-95DD-F1722B2800CC@.microsoft.com...
>