joining 2 tables onto one

Hi, I'm trying to do something really simple but am not coming up with the
right answer. I have master table A on which I am trying to outer join
(separately) tables B and C. So it should look like this:
Table A LEFT OUTER JOIN Table B
Table A LEFT OUTER JOIN Table C
I tried doing:
select ...
FROM Table A
LEFT JOIN Table B
on A.Name = B.Name
LEFT JOIN Table C
on A.Name = C.Name
But it looks like it's joining C onto B and A, instead of joining them
separately. How do I join them separately?
Thanks.Please elaborate on "looks like it's joining C onto B and A". What do you me
an?
ML
http://milambda.blogspot.com/|||I hate to say this, but I would recommend using the View developer screen,
which allows us to construct queries like in Access. Go to 'Views' and
select 'create a new view'. Add the tables you need. Don't even necessaril
y
save the view, just take the sql and use it in your code. It shouldn't be a
crutch but can be very instructional in how to code joins.
The order you put your tables into the view, and the order inwhich you
connect them will determin how 'clean' the sql it writes will be.
Also, for clarity there is always the dreaded *= syntax. While incredible
easy to read, some here will probably get very upset that I mentioned it.
Its supposed to go away, but its sooo darn easy to read. Don't tell anyone
you heard it from me. There are rumours that it is non-deterministic or
something, I've tested it a million times and had no problems yet.
ML - what is your take on *=, any opinions?
"Ryan D" wrote:

> Hi, I'm trying to do something really simple but am not coming up with the
> right answer. I have master table A on which I am trying to outer join
> (separately) tables B and C. So it should look like this:
> Table A LEFT OUTER JOIN Table B
> Table A LEFT OUTER JOIN Table C
> I tried doing:
> select ...
> FROM Table A
> LEFT JOIN Table B
> on A.Name = B.Name
> LEFT JOIN Table C
> on A.Name = C.Name
> But it looks like it's joining C onto B and A, instead of joining them
> separately. How do I join them separately?
> Thanks.|||I thought I made it very clear, but table B includes a where statement
specifying one condition and table C specifies a contrasting where statement
specifying another condition. I am comparing B and C against the same table
A. So I guess I'm asking how to do is (A intersects B) union with (A
intersects C).
"ML" wrote:

> Please elaborate on "looks like it's joining C onto B and A". What do you
mean?
>
> ML
> --
> http://milambda.blogspot.com/|||May be this is
select ...
FROM Table A
LEFT JOIN Table B
on A.Name = B.Name
LEFT JOIN Table C
on A.Name = C.Name
where B.field1 = ' xxx'
or c.field1 = 'yyy'
Regards
Amish|||The *= /=* join syntax still works, I've heard it's deprecated, and I've als
o
heard it isn't. I don't really see what would make it more readable than the
more contemporary qualified outer join syntax, but then again it just may be
in the eye of the beholder. I personally prefer explicit and qualified joins
,
plus I format the query to increase readability.
E.g.:
select ...
from A
inner join B
on B.Key = A.Key
lef join C
on C.Key = B.Key
right join D
on D.Key = A.Key
As for your query, Ryan, please post DDL, sample data and expected results.
It's very difficult to come up with a solution without seeing the actual dat
a.
ML
http://milambda.blogspot.com/|||> The *= /=* join syntax still works, I've heard it's deprecated, and I've alsoed">
> heard it isn't.
In SQL Server 2005, it will not work unless you set the database to compatib
ility level 80 or lower.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ML" <ML@.discussions.microsoft.com> wrote in message
news:0D4C7472-613F-4101-9F7B-E7E12E36F035@.microsoft.com...
> The *= /=* join syntax still works, I've heard it's deprecated, and I've a
lso
> heard it isn't. I don't really see what would make it more readable than t
he
> more contemporary qualified outer join syntax, but then again it just may
be
> in the eye of the beholder. I personally prefer explicit and qualified joi
ns,
> plus I format the query to increase readability.
> E.g.:
> select ...
> from A
> inner join B
> on B.Key = A.Key
> lef join C
> on C.Key = B.Key
> right join D
> on D.Key = A.Key
>
> As for your query, Ryan, please post DDL, sample data and expected results
.
> It's very difficult to come up with a solution without seeing the actual d
ata.
>
> ML
> --
> http://milambda.blogspot.com/|||Good to know. Thank you, Tibor. Not that I've ever used it. :)
ML
http://milambda.blogspot.com/|||Old-style outer joins is a mess and I'm happy MS are finally on the road to
get rid of it. I don't
think I failed up to this point to scare anyone off from using old style out
er joins with below
example. :-)
USE pubs
GO
DROP VIEW v
GO
CREATE VIEW v AS
SELECT p.pub_name, t.price, t.title
FROM publishers AS p, titles AS t
WHERE p.pub_id *= t.pub_id
AND t.price IS NOT NULL -- *** OBS ***
GO
--begrunda nedanst?ende resultat:
SELECT *
FROM v
SELECT *
FROM v
WHERE price IS NULL
SELECT *
FROM v
WHERE price IS NOT NULL
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ML" <ML@.discussions.microsoft.com> wrote in message
news:7DC3AF71-F65F-485E-BEE8-2BAC34970ECF@.microsoft.com...
> Good to know. Thank you, Tibor. Not that I've ever used it. :)
>
> ML
> --
> http://milambda.blogspot.com/|||Oh, the humanity!
Don't tell me this is by design...? It really is scary.
This actually makes me feel really good for always using the new join
syntax. :)
ML
http://milambda.blogspot.com/