MySQL Join and PHP Problem

[martinc126]

Hello,

I've got a very strange problem.

I'm rather new to MySQL so any help would be great.

Basically this is what i'm trying to do.

I have 1 Database called t_question

In this database i have inbox and outbox

Inbox has the following fields

source
dest
dtime
message
network
message_id
message_after_match
matched_keyword
matched_subkeyword

Outbox has the following fields

message_id
username
time
mobile
network
message
cost

As you can see message_id appears in both.

What i am trying to do is display the whole content of inbox on a single page, However if outbox.message_id has a message_id in, which matches a row in inbox.message_id has then it wont display this row.

I managed to get this to work fine with just one row in outbox, However when i add another row in outbox it does everything on the page and displays it twice.

I want it to display once, but not display a row if outbox has a message_id thats in inbox (Each message will have a different id) So message_id in inbox wont occur more than once.

--- This is what i currently have ---

$query = "SELECT outbox.message_id, inbox.message_id, inbox.source, inbox.dtime, inbox.network, inbox.message_after_match ".
"FROM inbox, outbox ".
"WHERE inbox.message_id != outbox.message_id ORDER BY inbox.dtime ASC";

Any help would be great.

Thanks

Martin

[edited by: coopster at 6:10 pm (utc) on May 5, 2008]
[edit reason] Removed unnecessary code [/edit]

[coopster]

Welcome to WebmasterWorld, martinc126.

You are using a Cartesian join which joins every record in the first file to every record in the second file creating a large result set and not what you are looking for here. You want to use a LEFT JOIN and find only those rows where the message id in the second table have a NULL value.

SELECT 
  inbox.message_id, 
  inbox.source, 
  inbox.dtime, 
  inbox.network, 
  inbox.message_after_match 
 FROM inbox 
 LEFT JOIN outbox ON (inbox.message_id = outbox.message_id) 
 WHERE outbox.message_id IS NULL 
 ORDER BY inbox.dtime ASC 
;