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.

Woops! MySql didn't start when the server rebooted!

One of my customers had this problem today. I got a text message, "Lisha! Can you look at the server and tell me why mysql isn't working? I rebooted the server and now the website has an error. Something about Error #2002 and the mysql.socket."

It turned out that when they rebooted their LAMP server, the mysql demon didn't turn on with the rest of the services like the apache http demon. Luckily, this is easy to fix. I ssh'd to their server and got to work.

First, I checked to see if my hunch was right:
mysqladmin ping
mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)'
Check that mysqld is running and that the socket: '/var/lib/mysql/mysql.sock' exists!

Yipes! Yup, you have no bananas (or mysql, as the case my be).

Next, I ran the mysql initialization script:
/etc/init.d/mysqld start
Starting MySQL: [ OK ]

Then check to see if it worked:
mysqladmin ping
mysqld is alive

Ah, yes. All better!


Next time, we can make mysql demon startup at boot time. On a Red Hat type system you can run the following code as root (or via sudo) to run the initialization script automatically at boot:
ln -s /etc/rc.d/init.d/mysqld /etc/rc.d/rc3.d/S98mysql