Excluding rows from a select depending on other table

Hi all,

This is my first posting here.. I tried to read all the docs, and find out what I needed myself, but I guess I can't figure it out. Would anyone be willing to shed some light on this?

I've got a DB with several tables in it, meant for an application where users of a certain community can help translate the site in their native language.

Strings:
id1, string1, string2, project, is_dual
(ID: The ID, String1 is a string, String2 is another string that's supposed to go together with String1 (For translating things like a FAQ it's easier to have the question and answer together, in my reasoning), project is the project this string is part of (the entire translation effort is split into several subprojects), is_dual indicates if the string2 field should be looked at).

Strings_translated:
id2, string_id, string1_trans, string2_trans, language, trans_by
(ID is the ID, string_id is strings.id, the _trans fields are translations of the related fields in Strings, language is an INT indicating which language the translation is in, trans_by is the user ID)

(some other tables for Language, Project and Users, which as are just ID-Name tables)

My problem is that I want to do a SELECT on all Strings that do not have a Translation yet. Without a subquery. I think I got the subquery version, but I need this to run on a server running MySQL 3.x, and perhaps other DB's as well.



Would anyone be willing to help me by converting this to a join? I tried it myself, and either I got all results, or no results at all... If you're willing to help me, please also explain what your code does, so I can learn from it

Thank you for your time,

Sotha Sil

SELECT s.*
FROM strings s INNER JOIN strings_translated t ON s.id1 = t.id2
WHERE t.id2 IS NULL
AND project = ...

id2 is null only when the record doesn't
exist.

Thanks for looking into this. Unfortunately it doesn't work. The query you gave me seems to always give back 0 results

Any suggestions on what I'm doing wrong?

Edit: After reading up on advanced joins, I figured I needed a Left instead of Inner join. Behold.. it works.