Insert where row does not exist

kiwis80

So I've previously done a query on my fields, if no rows are returned then do a separate insert command.

That's okay but it's long winded.

What I have is a table with cells Team1ID, Team2ID, $CourtID

I have a code which needs to add a match if they've not previously played each other.

So INSERT Team1ID, Team2ID, $CourtID where not exists Team1ID = X and $Team2ID = Y BUT Team1ID could be Y and Team2ID could be X and that still shouldn't add, as they're played it's just the ID's are around the other way.

Is there a shorter way of doing this?

NogDog

I think it has to be at least 2 queries total, since you cannot do a where clause in an insert (at least not in MySQL, not sure about others). But you could at least have the first selection be a single query with something like:

select count(*)
from the_table
where
  (Team1ID = 1111 and Team2ID = 2222) or
  (Team1ID = 2222 and Team2ID = 1111)