Query Help

I have a database table with data like this (some fields ommited):
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