Friday, March 30, 2012
Query Help
reserveID owner creator
-- -- --
39009 1 0
39009 0 1
39100 0 1
I would like to do a query that would return all rows in which a reserveID
has a row with creator = 1 but no rows with owner = 1. So with the rows in m
y
example above, 39100 would be returned by my query but 39009 would not.
I've been experimenting with different queries with out much luck. How would
I accomplish this?Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
You might also want to learn why rows and columns are nothing
whatsoever like records and fields. You will never write RDBMS until
you do. Let's make a wild guess with bad names, no constraints, etc.
CREATE TABLE Reservations
(reservation_nbr INTEGER NOT NULL PRIMARY KEY,
reservation_owner INTEGER NOT NULL,
reservation_creator INTEGER NOT NULL);
SELECT R1.reservation_nbr, R1.reservation_owner,
R1.reservation_creator
FROM Reservations AS R1
WHERE R1.reservation_creator = 1
AND NOT EXISTS
(SELECT *
FROM Reservations AS R2
WHERE R2.reservation_owner = 1
A ND R1.reservation_creator = 1);|||Hi There,
I think this will solve your problem.
Select * From tmpData4 T4 Where cr = 1 And
Not Exists
(
Select * from tmpData4 T5 Where own = 1 And T5.res = T4.res
)
Where tmpdata4 is your tableName
With Warm regards
Jatinder Singh|||select * from <tblname> where creator = 1 and reserveID not in (select
distinct reserveid from <tblname> where owner = 1)
i hope this will work
with reagards
Rajeev Shukla|||not sure what your requirements are, but a let me have a shot in the
dark, assuming that reserveID is not nullable:
select * from your_table
where reserveID in(select reserveID from your_table where creator = 1)
and reserveID NOT in(select reserveID from your_table where owner = 1)
if reserveId is nullable , go for EXISTS/NOT EXISTS instead of IN/NOT IN
Friday, March 9, 2012
Query dbowner
We have a script that allows customers to change the users of the
database including the database owner. This script can be run at any
time. However, sometimes (and it really is only sometimes!) when the
following statement executes:
"exec sp_changedbowner @.USER_OWNER"
the following error is reported:
"The proposed new database owner is already a user in the database."
I have checked the other postings and the Books Online but cannot find
a way to query the current name of the dbowner i.e. the value of
@.USER_OWNER above. We have to be able to do this within the script so
that if the @.USER_OWNER variable is already the dbowner we do not
bother to execute the statement as otherwise this could display the
error. Can anybody help?
Thanks in Advance
PaulEM gets it from exec sp_MSdbuseraccess N'db', N'%'
which calls
select o.name, o.version, o.crdate, suser_sname(o.sid), o.dbid, o.status,
o.category, o.status2, DatabaseProperty(o.name, N'isfulltextenabled')
from master.dbo.sysdatabases o where o.name = @.qual
so
select suser_sname(o.sid) from master.dbo.sysdatabases where name = 'DBNAME'
should do it.
Danny
<paulwragg2323@.hotmail.com> wrote in message
news:1102498820.367939.212520@.f14g2000cwb.googlegr oups.com...
> Hi
> We have a script that allows customers to change the users of the
> database including the database owner. This script can be run at any
> time. However, sometimes (and it really is only sometimes!) when the
> following statement executes:
> "exec sp_changedbowner @.USER_OWNER"
> the following error is reported:
> "The proposed new database owner is already a user in the database."
> I have checked the other postings and the Books Online but cannot find
> a way to query the current name of the dbowner i.e. the value of
> @.USER_OWNER above. We have to be able to do this within the script so
> that if the @.USER_OWNER variable is already the dbowner we do not
> bother to execute the statement as otherwise this could display the
> error. Can anybody help?
> Thanks in Advance
> Paul|||This works fine (except missing o for alias for
master.dbo.sysdatabases) and is just what I needed.
Thanks Danny.|||This works fine (except missing o for alias for
master.dbo.sysdatabases) and is just what I needed.
Thanks Danny.|||One method to retrieve the current database owner:
DECLARE @.CurrentOwner nvarchar(256)
SELECT @.CurrentOwner = SUSER_SNAME(sid)
FROM master..sysdatabases
WHERE name = DB_NAME()
You can then conditionally change the owner:
IF @.CurrentOwner <> @.USER_OWNER
EXEC sp_changedbowner @.USER_OWNER
However, your error message is most likely because the new database owner is
a current database user rather than the database owner. The query below
will identify this condition as well as the database owner.
SELECT
l.name as LoginName,
u.name AS UserName
FROM master..syslogins l
JOIN sysusers u ON u.sid = l.sid
WHERE l.sid = SUSER_SID(@.USER_OWNER)
--
Hope this helps.
Dan Guzman
SQL Server MVP
<paulwragg2323@.hotmail.com> wrote in message
news:1102498820.367939.212520@.f14g2000cwb.googlegr oups.com...
> Hi
> We have a script that allows customers to change the users of the
> database including the database owner. This script can be run at any
> time. However, sometimes (and it really is only sometimes!) when the
> following statement executes:
> "exec sp_changedbowner @.USER_OWNER"
> the following error is reported:
> "The proposed new database owner is already a user in the database."
> I have checked the other postings and the Books Online but cannot find
> a way to query the current name of the dbowner i.e. the value of
> @.USER_OWNER above. We have to be able to do this within the script so
> that if the @.USER_OWNER variable is already the dbowner we do not
> bother to execute the statement as otherwise this could display the
> error. Can anybody help?
> Thanks in Advance
> Paul|||Thanks to both of you for replying to my post.
I have now solved this and proven that both methods will work.
Thanks.