Monday, July 21, 2008

mySQL query trouble

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?

*edit* I got a message suggesting that I ought to just do uid is null and ignore the uid != 1 part. But here is why that doesn't work for me...

Let's say that I want user 2 to get all the strings that she hasn't translated into Spanish yet.


mysql> select distinct tnid from fr_strings as st natural left join fr_tr_by_uid as ui where sid > 279 AND (uid is null OR language != "es");
+------+
| tnid |
+------+
| 872 |
| 873 |
+------+


This doesn't work. You see, uid 2 DID translate 870 into Spanish, but not 871 (see the first results table). I need her to get 871 for translation still, but this query won't give it to her. I'm still stuck.

3 comments:

yhager said...

Have you tried using '<>' instead of '!=' ?

I believe '!=' does not work on MySQL

Lisha said...

Nah, in mySQL 5.0 != definitely works.

mySQL operators

yhager said...

Oh, good to know '!=' works :)

Anyway, looking at this again, if you need all strings that user U did not transalte to language L, then you need all cases where (uid=U and language=L) is not true. Negating that you get:

... WHERE uid!=U OR language!=L


PS: leaving a comment here is a real pain.. Do I really have to answer the CAPTCHA for *every* comment, even more than once of the same comment???