MySQL Join Help

join type? right left inner outer?

[fl_diosa]

I need help with rewriting a sql query. Nested queries that work in MS databases apparently do not work in MySQL databases. Here is what I need in MS query pseudo code:

SELECT table1.myfield1, table2.myfield2
FROM table1, table2
WHERE table1.key=table2.key AND myfield1="something"
AND myfield2 NOT IN
(SELECT myfield2 FROM table3 WHERE table3.myfield3="whatever")

For example, if I wanted to get all Forum Posts (Table1) for TopicA that do NOT have replies (Table3) by User1.

Any help would be greatly appreciated!

[coopster]

Welcome to WebmasterWorld, fl_diosa!

Starting with version 4.1, MySQL supports all subquery forms and operations which the SQL standard requires, as well as a few features which are MySQL-specific.

For MySQL versions prior to 4.1, most subqueries can be successfully rewritten using joins and and other methods. Rewriting Subqueries for Earlier MySQL Versions [mysql.com].

You are probably going to end up using a

LEFT JOIN

where the linked value in the right hand table

IS NULL

.

SELECT 
 table1.myfield1, table2.myfield2 
 FROM table1 
 INNER JOIN table2 
 ON table1.key = table2.key 
 LEFT JOIN table3 
 ON table2.myfield2 = table3.myfield3 
 WHERE table1.myfield1="something" 
 AND table3.myfield3="whatever" 
 AND table2.myfield2 IS NULL 
;

I don't have a database structured as such to test the statement, but it should give you a start.