select statement with a join

[Guest_imported]

I have two tables: 1) request, 2) n2t

the important fields in the request table are rID and rStatus
the important fields in the n2t table are n2tNoteID and n2tTicketID.

what i need to do is be able to select all rIDs where rStatus is > O, and exclude any rID's that match n2tTicketID where n2tNoteID equals a specific number

In other words if the following data is available:
(rID = n2tTicketID)
Requests N2T
rID rStatus n2tTicketID n2tNoteID
1 1 1 3
2 0 1 5
3 3 2 3
4 2 3 2
5 0 4 1

and i want all the rID's where the rStatus is > 0 excluding any that are linked to n2TicketID #5 the result should be
--rID = 3, rID = 4

I don't know how to make this select statement, I've tried different things like JOIN, sub-selects, but nothing has worked. Thank You in advance.

 

[KarveR]

I think this should get close ...just how close, you tell me ;-)

SELECT r.ID, r.Status, n.NoteID, n.TicketID FROM request r, N2T n WHERE r.ID = n.TicketID and r.status > 0 and n.TicketID != '5'; ______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[Guest_imported]

that's pretty close, but the problem with it is that it doesn't include the rIDs that don't exist in the N2T table

for instance: appending to the data i posted earlier, lets change the record where rID = 5 to an rStatus of 3 so it looks like this
rID rStatus
5 3

if i use the syntax you suggested, it will only show the rID's that are also in the N2T table, but it will not include rID = 5 which i need

so the new result should only have these following rIDs:
3, 4, 5

 

[KarveR]

SELECT r.ID, r.Status, n.NoteID, n.TicketID FROM request r, N2T n WHERE r.status > 0 and n.TicketID != '5'; ______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[KarveR]

nope that last one will fail as you dont link the request to the ticket correctly......
thinking some more. ______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[Strannik]

What about following query:

select * from Requests a left outer join n2t b
on a.rID =b.n2ticketid AND b.n2tNodeID = 5
WHERE a.Status >0 AND b.n2ticketid is Null

 

[KarveR]

Do you have any field which is common and unique to both tables like a ticket number? it seems your tickedID does not always match requestID or am I worng.
As I see it you should have a commonm element to both tables to make it all tie up correctly and without this you will get incorrect results (as proven).
______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[Guest_imported]

rID from the Requests table and n2tTicketID from the N2T table is the same id number. but in the N2T table, n2tTicketID is not a unique number. in other words, you can have one ticket with multiple notes and also one not
e assigned to multiple tickets. In other words i have a request table that contains the rID or ticket number, a notes table that contains notesID and "middle-man" table (N2T) which connects a ticket to a note. so here is
the broader scope of what I want to do

Requests Notes
rID rStatus nID content
1 1 1 text
2 0 2 more text
3 3 3 other text
4 2 4 more text
5 3
so now i want to assign ticket 1, 3 and 4 to note 3. Also assign ticket 3 and 1 to note 1. My N2T table will look like this.
N2T
TicketID NoteID
1 3
3 3
4 3
1 1
3 1

so now i want to be able to get a listing of all the rID/TicketID that
have an rStatus > 0 and are NOT assigned to NoteID 1. The result
i SHOULD get is
(rID=4,rID=5).
basically the SQL u gave me the first time works close to perfect
with the exception that in excludes ticketID/rID 5, since it isn't in
the N2T table.

I'm thinking of using the SQL you suggested and using an OR
operand somewher, not sure where yet.

Thanks for all ur help so far.

 

[KarveR]

Try this then, it checks against the noteID instead, howver if you need it to also check the ticketID use the second one:
1)
SELECT r.ID, r.Status, n.NoteID, n.TicketID FROM request r, N2T n, Notes o WHERE r.ID = n.TicketID and r.status > 0 and o.ID != '1';

2)
SELECT r.ID, r.Status, n.NoteID, n.TicketID FROM request r, N2T n, Notes o WHERE r.ID = n.TicketID and r.status > 0 and o.ID != '1' and n.TicketID != '5'; ______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.