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/

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/

Problem with domain group and diagrams

I have a problem and I dont have an idea to solve it. I work with SQL Server and my students on Faculty of Information Technology.

I have create SQL Server login for domain group of users (about 60 od them). That mean I have SQL Server login like this Domain\Users To whole group of users I had grant server role (dbcreator). I dont wish to import 60 login one by one and gice that permision. Ok, student can create database without any problems But, when they try to create database diagram (expanding database diagram node). They get a message that database need to have a valid owner (Hmm..ok its not a big deal to do it) Quick check to some of database (Right click-->properties-->General-->Owner (ther is a Domain\UserName (not name of group)..and that is ok (Right click-->properties-->Files-->TextBox Owner. I try to enter same Domain\UserName from step 5. But I get this message

TITLE: Microsoft SQL Server Management Studio

Set owner failed for Database '1110_EvidencijaKnjiga'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Set+owner+Database&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred in SMO. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&LinkId=20476

The login 'Domein\UserName' does not exist on this server.

BUTTONS:

OK

This is wired. Ok I know that that user is not login but group is. Step 5 show that user, but step 6 have empty owner options and dose not allowe me to enter same user.

How to fix this?

Thank you

Try changing the database owner to a valid login by executing the following SQL statement:

ALTER AUTHORIZATION ON DATABASE::YourDatabaseName TO sa

-Sue

|||

Sue Hoegemeier wrote:

Try changing the database owner to a valid login by executing the following SQL statement:

ALTER AUTHORIZATION ON DATABASE::YourDatabaseName TO sa

-Sue

Thank you Sue for your advice and solution of my problem.

But I am not happy with your solution of problem. Here is why.

Ok, I change owner to sa. But now domain user who is create that database, have not aprorpiate permission. (dont forget I have import doman group on SQL Server..not user one by one). Now I must cretae account on SQL Server for that doman user and give him (or her) db_owner database role. In my case that step I need to repeat about 30 times. In that case doman groups on SQL Server are not cool solution in cases like mine.

Is there any other solution or if somene can expplan why this is hapening? Why user from doman gruop with create database role, can not be a valid owner (in terms of SQL error message)?

Jasmin

|||

Make sure the latest service packs have been applied to all the PCs with the client tools. There were some fixes for related issues on SP1. Other than that, you could have them use Visio instead of the diagram tool in Management Studio.

-Sue