Problem with LEFT JOIN

I've tried LEFT JOIN and UNION but nothing worked as it should be.
It would be nice if anybody has an idea what I could do.
I have 2 Tables
TABLE1
ID NAME
1 Anton
2 Bea
3 Cesar
4 Dora
5 Emil
TABLE2
NR ID
22 1
22 3
33 4
33 3
44 5
my output should be a full list of TABLE1 and marked with the number fro= m =
TABLE2 but only if it is 22
OUTPUT
ID NAME NR
1 Anton 22
2 Bea NULL
3 Cesar 22
4 Dora NULL
5 Emil NULL
-- =
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/Try:
select
t1.*
, t2.NR
from
Table1 t1
left join
Table2 t2 on t2.ID = TD.ID and T2.NR = 22
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Matthias Kreusch" <mk@.loft-net.de> wrote in message
news:op.s979wodzifpv2o@.gollom.loft-net.local...
I've tried LEFT JOIN and UNION but nothing worked as it should be.
It would be nice if anybody has an idea what I could do.
I have 2 Tables
TABLE1
ID NAME
1 Anton
2 Bea
3 Cesar
4 Dora
5 Emil
TABLE2
NR ID
22 1
22 3
33 4
33 3
44 5
my output should be a full list of TABLE1 and marked with the number from
TABLE2 but only if it is 22
OUTPUT
ID NAME NR
1 Anton 22
2 Bea NULL
3 Cesar 22
4 Dora NULL
5 Emil NULL
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/|||Thx for the fast reply but it didn't work.
Access says: "Error 3296 Join expression not supported."
it's because T2.NR =3D 22 is not supported and if I put this line in a W=HERE =
statement then I get only 2 rows.
On Sat, 27 May 2006 23:13:04 +0200, Tom Moreau <tom@.dont.spam.me.cips.ca=> =
wrote:
> Try:
> select
> t1.*
> , t2.NR
> from
> Table1 t1
> left join
> Table2 t2 on t2.ID =3D TD.ID and T2.NR =3D 22
>
-- =
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/|||not supported?
What is your RDBMS server?
if you want it in the where clause, try this:
where (t2.nr is null or t2.nr = 22)
"Matthias Kreusch" <mk@.loft-net.de> wrote in message
news:op.s99hq0xjifpv2o@.gollom.loft-net.local...
Thx for the fast reply but it didn't work.
Access says: "Error 3296 Join expression not supported."
it's because T2.NR = 22 is not supported and if I put this line in a WHERE
statement then I get only 2 rows.
On Sat, 27 May 2006 23:13:04 +0200, Tom Moreau <tom@.dont.spam.me.cips.ca>
wrote:
> Try:
> select
> t1.*
> , t2.NR
> from
> Table1 t1
> left join
> Table2 t2 on t2.ID = TD.ID and T2.NR = 22
>
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/|||It IS supported - in SQL Server.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Matthias Kreusch" <mk@.loft-net.de> wrote in message
news:op.s99hq0xjifpv2o@.gollom.loft-net.local...
Thx for the fast reply but it didn't work.
Access says: "Error 3296 Join expression not supported."
it's because T2.NR = 22 is not supported and if I put this line in a WHERE
statement then I get only 2 rows.
On Sat, 27 May 2006 23:13:04 +0200, Tom Moreau <tom@.dont.spam.me.cips.ca>
wrote:
> Try:
> select
> t1.*
> , t2.NR
> from
> Table1 t1
> left join
> Table2 t2 on t2.ID = TD.ID and T2.NR = 22
>
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/