mySQL query solution

[lishablog]

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

Comments

[justbeast]

Can you give a tiny bit of context - what is the fr_strings and the fr_tr_by_uid table? (Sounds like french/internationalization?).
It'd help me think about this -- I have a feeling this can be solved by an outer join of the two tables, but I'm not sure.

[justbeast]

Ah, ok, I didn't see your previous post..

[lishablog]

fr_strings is "fring strings" and "fr_tr_by_uid" is "fring translations by user id".

As for context, the entry I wrote earlier (http://lishablog.livejournal.com/81094.html) explains in painful detail. :) You probably just haven't gotten to that one yet.

little controversion

[justbeast]

There is no tnid 283, there are sid 283 and tnid 873 - which one do you want?

"What I want out of this bunch here is tnid 283. That's all. That's the ONLY one I want." - looking at joined tables that translates to "I want those sid/tnid that are only in left table i.e. missing in right table then this should do:
select distinct st.tnid from fr_strings as st LEFT JOIN fr_tr_by_uid as ui ON st.sid=ui.sid where st.sid > 279 AND ui.uid IS NULL;
-- OR
SELECT st.tnid from fr_strings as st LEFT JOIN fr_tr_by_uid as ui ON st.sid=ui.sid WHERE st.sid > 279 AND ui.uid IS NULL
GROUP BY st.tnid;
-- OR with some aggregates :-)
SELECT st.tnid, SUM(ui.uid) from fr_strings as st LEFT JOIN fr_tr_by_uid as ui ON st.sid=ui.sid WHERE st.sid > 279
GROUP BY st.sid, st.tnid, st.uid
HAVING SUM(ui.uid)=0;

If you want e.g.: First (I suppose MIN) untranslated tnid for a given user then:
SELECT MIN(st.tnid) from fr_strings as st LEFT JOIN fr_tr_by_uid as ui ON st.sid=ui.sid WHERE st.sid > 279 AND (ui.uid!=1 OR ui.uid IS NULL)
GROUP BY st.sid;