Which JOIN shall I use?

compsci · #1 (**permalink**) 12-15-08, 10:26

Hello all,

I would like to JOIN two tables but there may be more matches in table 2, for example:

Code:

table 1    table 2

ID           ID   emailto

id1          id1  yes
id2          id1  no
id3          id1  no

I would like to match only one row from table 2 to table 1 that has a certain field not equal to 'yes'. How can I do this, I have this so far:

Code:

SELECT * from message INNER JOIN receiver ON message.msgID=receiver.msgID 
WHERE (message.threadID=$threadID OR message.msgID=$calledRecord OR message.msgID=$threadID) 
AND receiver.emailto=!'yes' ORDER BY message.date ASC

It seems to work but I am not so sure, anyone confirm? Am I using the right sort of JOIN?

r937 · #2 (**permalink**) 12-15-08, 10:54

INNER JOIN is correct because you want at least one receiver

but matching only one receiver, that's a different problem

gvee · #3 (**permalink**) 12-15-08, 10:56

Code:

SELECT *
FROM   table1
 INNER
  JOIN (
        SELECT id
        FROM   table2
        WHERE  emailto = 'yes'
       ) x
    ON table1.id = x.id

But what happens when there's more than one yes value for one id?

compsci · #4 (**permalink**) 12-15-08, 11:04

Quote:

Originally Posted by georgev

But what happens when there's more than one yes value for one id?

This should neve occur unless my application makes a mistake. If it does make a mistake I will have to fix that failure, but I'm not sure how to minimize the effects of that mistake.

compsci · #5 (**permalink**) 12-15-08, 11:06

Thanks for the help guys, I will test out the query and report back.

gvee · #6 (**permalink**) 12-15-08, 11:08

Quote:

Originally Posted by compsci

This should neve occur unless my application makes a mistake. If it does make a mistake I will have to fix that failure, but I'm not sure how to minimize the effects of that mistake.

It's called a unique constraint / unique index.
Apply it across your id and emailto column

Internet Services

Web Development

Digital Marketing

Consumer Tech