mySQL query solution

I had been trying very hard to avoid having to do a nested query, but in the end, I really couldn't figure out a way around it. This is the query that gets the job done:


mysql> select tnid from fr_strings where sid NOT IN (SELECT sid from fr_tr_by_uid where uid = 1 and language = 'es');


If you have a non-nested solution for this problem, I'd still love to see it!

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.

Finding a host I couldn't see

Last week I finally broke down and asked IT if they would mind if I switched over to a Linux desktop for my daily development work. Functioning in Windows was driving me absolutely bonkers. He wasn't so sure about it at first. He suggested that I run Windows and then have Linux running in virtualization. That just seemed like a waste of resources to me, though. So, I suggested that I set the computer up for dual boot, and if things went badly I could always go back to Windows.

The IT guy's concern was that, while I might be able to do my programming in Linux, I might not have access to all the company shares, the exchange server for mail, and all that other Windows-based stuff sitting on the company network. And he was right to be concerned. In the past, all those things have been a major pain in the backside to fix.

The good news is that Samba, the tool that lets you hook up Linux or Unix machines to a Windows network, has come a very long way, and the whole thing turned out to be pathetically easy. Not only that, but Evolution for mail kicks the proverbial butt, and I was able to get mail, tasks, contacts, and calendar from exchange working perfectly with the Exchange server with just a few button clicks and no sweat at all.

But, then I need to look something up on the company wiki, and that's when I hit trouble. Firefox turned my http://companywiki/ url into http://www.companywiki.com. Woops! I tried a couple of times before it sunk in to my thick skull that the problem was that my computer wasn't recognizing the internal domain names. Clearly I need another Domain Name Server in my list, but the problem is that I don't want to go back to that IT guy just now. He may just tell me it's all Linux's fault, and that I have to switch back. I don't want that. So, I have a work around.

I checked in Windows what the ip address for companywiki is supposed to be, and then, back in Linux, I edited my hosts file to point the name companywiki to that address. Poof! I have normal access to the wiki using the domain name now. Easy peasy.

There are other uses for the hosts file, too. For instance, if I have server settings in a Web application that I'm working on, I can tell my hosts file that those server names are really my computer. My computer will look where the hosts file tells it to look before asking the Domain Name Server for information, so I can test the application without having to change those server name variables in the code.

Linux is not the only system with a hosts file that can let you find servers by a certain name, either. You can find a list of where to find the hosts file on different operating systems at Wikipedia.