Trouble formulating tricky query 2

[grimep]

Am using PHP/MySQL, though that should be fairly irrelevant..

I have two tables and am trying to write a query that does a simple selection on the first table (on thrID), and if there is a corresponding record in the other table and a match on another selected field (userID) indicate that with a value in one of the SELECTed columns, if not leave the value as null.

table m
ID
thrID
txtField

table u
ID
mID // used to show relationship with ID in m table
thrID // same as thrID in table m
userID


For example, if the data in table m is
ID thrID txtField
1...1....."xxx"
2...1.....""yyy"
3...1.....""zzz"

and table u:-
ID mID thrID userID
1..1....1......2
2..3....1......2

we should get the result:-
m.mID m.txtField u.userID
1......xxx........2
2......yyy........null
3......zzz........2


In English, the query is:-
Return records from m where thrID = $threadID, if there is a record in u with u.userID = $user AND mID = u.mID then u.userID value should also be returned, if no match leave u.userID null

Trying to translate into SQL I get this far:-
SELECT m.ID, m.txtField, u.userID FROM m, u WHERE m.thrID = $threadID AND u.userID = $user AND u.msgID = m.ID

The fields being compared are right, but the logic obviously isn't.

I have tried an outer join on table m, but that gets all messages. If SQL let you nest SELECTS and have the result of one as the table in the other SELECT you could do it something like:-

SELECT DISTINCT * FROM (SELECT <fields> FROM m LEFT OUTER JOIN u where u.userID = $user AND u.msgID = m.ID), m WHERE m.thrID = $threadID

Is that nearly right?!

The only way out I can see at the moment is to split it into 2 queries, and compare them at the client end, which would hammer system performance.

 

[swampBoogie]

SELECT <fields> FROM m LEFT OUTER JOIN u on u.userID = $user AND u.msgID = m.ID WHERE m.thrID = $threadID

 

[grimep]

thanks, I think you've saved my bacon. It looks so simple and obvious when you see it written... I guess one day I'll be thinking in SQL !