Aaagh. My head has gone all fuzzy, I don't know whether it's my application logic or my SQL that's fubar'd.

In my application, Users can send Invites to recipients, and those recipients - who are Users themselves - can send Greetings back.

The relationship between users is managed via the UserContacts table, which basically just joins the Users table twice - once for idUsers, once for idContacts.

When Bob sends an Invite to Andy , the UserContact ID used is the one where Bob is the User ID, and Andy is the Contact ID.

But when Andy replies to Bob's invite with a Greeting, the UserContact ID used is the one with Andy as the UserID and Bob as the Contact ID.

When the Greeting is received, I need to mark that Invite as having been responded to. But for that I need the UserContact ID for the Invite, and by that time the only UserContact ID I have is the wrong way round; it's for the Greeting.

So my problem is this: given the UserContact ID of the Greeting, and a User ID (the user who's currently logged in, who just sent the Greeting), how do I get the UserContact ID of the Invite that was sent to them?

I'm doing it with two queries, and I know it's possible with one. But - and given that you've read this far, you're probably appreciating why - my head just can't figure out the join required.

Here are the two queries I'm using. First, get the Contact ID of the UserContact ID for the Greeting in question:

SELECT idContacts FROM UserContacts WHERE idUserContacts = the-usercontact-ID-of-the-greeting

Then get the UserContact ID where the User ID is the value I've just pulled out, and the Contact ID is the ID of the currently logged-in user. This will be the UserContact ID that was applied to the original Invite:

SELECT idUserContacts FROM UserContacts WHERE idUsers = whatever-the-query-above-returned AND idContacts = the-current-logged-in-user-id

I'm just querying the table twice, and I KNOW there's a join possible in there to make it more efficient, but I can't figure out what it is.