Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2007
    Location
    London
    Posts
    117

    Unanswered: Which JOIN shall I use?

    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?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    INNER JOIN is correct because you want at least one receiver

    but matching only one receiver, that's a different problem
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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?
    George
    Home | Blog

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    117
    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.

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    117
    Thanks for the help guys, I will test out the query and report back.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •