SQLite - No Such Column TABLE.ROWID
your first query is standard SQL (the parentheses are redundant, only needed by MS-Access) and should work just fine in SQLite, with or without the parentheses:

SELECT 
    Message.messageContent, Message.messageDateTime 
FROM
        Message 
    INNER JOIN 
        ( Contact INNER JOIN contact_m2m_message 
            ON Contact.contactID = contact_m2m_message.contactID
        )
      ON Message.msgID = contact_m2m_message.messageID ;


Edit: Actually, SQLite needs the parentheses, too, at least the version provided in SQL-Fiddle.



The 2nd query is not valid because you have not provided an alias for the derived table:

SELECT 
    Message.messageContent, Message.messageDateTime 
FROM 
        Message 
    INNER JOIN 
        ( Select Contact.contactNumber, Contact.contactName 
          FROM Contact INNER JOIN contact_m2m_message 
               ON Contact.contactID = contact_m2m_message.contactID
        )     --&lt;-------------------------------------- no alias
            ON Message.msgID = contact_m2m_message.messageID;


To make it  work wirh a derived table, you have to provide an alias for the derived table and also add the contact_m2m_message.messageID in the SELECT list of the derived table (and remove columns that are not used):

SELECT 
    Message.messageContent, Message.messageDateTime 
FROM 
        Message 
    INNER JOIN 
        ( Select -- Contact.contactNumber, Contact.contactName,
                 contact_m2m_message.messageID 
          FROM Contact INNER JOIN contact_m2m_message 
               ON Contact.contactID = contact_m2m_message.contactID
        ) AS cm
            ON Message.msgID = cm.messageID;




But I don't think you should use that. The first query should work. You can re-arrange the order how of the tables are joined. Using table aliases also helps in readibility:

SELECT 
    m.messageContent, m.messageDateTime 
FROM 
    Message  AS m
  INNER JOIN
    contact_m2m_message  AS cm
      ON m.msgID = cm.messageID 
  INNER JOIN
    Contact  AS c 
      ON  c.contactID = cm.contactID ;