Help with mysql query across three tables

Saeven

Hello,

A bit of a mindbender here:

Three tables are involved:

Table objects:

tid <- key
open
date (timestamp)

Table locks:

tid

userid

*These are a multikey

Table dispatch:

tid <- key
userid_from
userid_to

The idea is that Table A contains objects, Table B contains a list of locks, and the third table contains a dispatch list if you will of what objects are assigned to what.

For this application, it is important to get a list of objects that are open, that also are not locked or are locked by a given user, for a given user X. I have this part done with this:

SELECT * FROM objects LEFT JOIN locks ON objects.tid=locks.tid WHERE objects.open='1' AND ( locks.userid IS NULL OR locks.userid='X' ) ORDER BY objects.date;

Now, these objects can be dispatched also to other users, and it becomes a task to get all the above objects, excluding those that are dispatched, except for those that are dispatched to the user X we are querying for.

So primarily...

Get all object tid's for a user X:
- where objects.open = 1
- where there is no lock
- where if there is a lock, it must be locked to a user X

MINUS

the set of object tids that are dispatched, unless they are dispached to X

This would be easy if there was an intersection clause

It would be the query above, minus the intersection of those queries above where the tid value exists and is not associated to X.

Sorry for the confusion, it has me confused obviously.

Thanks!!

Saeven

In short...

SELECT * FROM objects LEFT JOIN locks ON objects.tid=locks.tid WHERE objects.open='1' AND ( locks.userid IS NULL OR locks.userid='X' ) ORDER BY topics.date;

minus..

SELECT * FROM dispatch WHERE userid_to != 'X'

Based on tid, where X is the only variable.

dnszero

SELECT * FROM objects 
WHERE objects.open = 1 AND (objects.tid IN (SELECT locks.tid FROM locks WHERE locks.userid='X') OR objects.tid NOT IN (SELECT tid FROM locks));

Gotta check that one for syntax errors but it basically meets the the requirements you layed out.

Get all object tid's for a user X:
- where objects.open = 1
- where there is no lock
- where if there is a lock, it must be locked to a user X

Hope that helps, if its not quite right maybe it'll at least give you a starting point.