mySQL query problem
I'm having a hellish time with a sql statement. I need to get nodes ("tnid" here) where a given user (uid = 1) has not translated a given language (language = "he", or rather, != as you'll see)
fr_strings has sid and tnid (those are the only ones important to us now, anyway)
fr_tr_by_uid has sid, uid and language
When someone translates a string, it pops in their user id, the string id and the language that they translated the string to. So, say, if I am translating into es or he (spanish or hebrew) I should no longer see the string pop up for translation in es if I've already done that translation, but the request for the string translation would still pop up when I'm working on the he translation. Get it? OK.
So, I want to select the tnid's where uid != 1 and language != 'he'. Something like that. Let's look at some queries and responses:
mysql> select distinct * from fr_strings as st natural left join fr_tr_by_uid as ui where sid > 279;
+-----+------+------+----------+
| sid | tnid | uid | language |
+-----+------+------+----------+
| 280 | 870 | 2 | es |
| 280 | 870 | 1 | es |
| 281 | 871 | 1 | es |
| 283 | 873 | NULL | NULL |
+-----+------+------+----------+
What I want out of this bunch here is tnid 283. That's all. That's the ONLY one I want. But if I try this:
mysql> select distinct tnid from fr_strings as st natural left join fr_tr_by_uid as ui where sid > 279 AND uid != 1;
+------+
| tnid |
+------+
| 870 |
+------+
That's clearly not what I wanted.
How about This:
mysql> select distinct tnid from fr_strings as st natural left join fr_tr_by_uid as ui where sid > 279 AND uid != 1 OR uid is null;
+------+
| tnid |
+------+
| 870 |
| 873 |
+------+
Still not there. As you can see, since the tnid shows up once in the table under a different uid, I still get that tnid back, even though one record with that tnid does have the uid=1.
I'm sure that this is something really stupid. (I'm always sure when I can't figure something out that the solution is something really stupid.) But, I can't shake the answer out of my head. Any of you know what I'm doing wrong?
fr_strings has sid and tnid (those are the only ones important to us now, anyway)
fr_tr_by_uid has sid, uid and language
When someone translates a string, it pops in their user id, the string id and the language that they translated the string to. So, say, if I am translating into es or he (spanish or hebrew) I should no longer see the string pop up for translation in es if I've already done that translation, but the request for the string translation would still pop up when I'm working on the he translation. Get it? OK.
So, I want to select the tnid's where uid != 1 and language != 'he'. Something like that. Let's look at some queries and responses:
mysql> select distinct * from fr_strings as st natural left join fr_tr_by_uid as ui where sid > 279;
+-----+------+------+----------+
| sid | tnid | uid | language |
+-----+------+------+----------+
| 280 | 870 | 2 | es |
| 280 | 870 | 1 | es |
| 281 | 871 | 1 | es |
| 283 | 873 | NULL | NULL |
+-----+------+------+----------+
What I want out of this bunch here is tnid 283. That's all. That's the ONLY one I want. But if I try this:
mysql> select distinct tnid from fr_strings as st natural left join fr_tr_by_uid as ui where sid > 279 AND uid != 1;
+------+
| tnid |
+------+
| 870 |
+------+
That's clearly not what I wanted.
How about This:
mysql> select distinct tnid from fr_strings as st natural left join fr_tr_by_uid as ui where sid > 279 AND uid != 1 OR uid is null;
+------+
| tnid |
+------+
| 870 |
| 873 |
+------+
Still not there. As you can see, since the tnid shows up once in the table under a different uid, I still get that tnid back, even though one record with that tnid does have the uid=1.
I'm sure that this is something really stupid. (I'm always sure when I can't figure something out that the solution is something really stupid.) But, I can't shake the answer out of my head. Any of you know what I'm doing wrong?