SQLite - No Such Column TABLE.ROWID I'm using this query on SQLite 3:

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;


it gave me this error: No Such Column: TABLE.ROWID

i'm confused as the very query was working fine when i tested over MS Access DB Engine just for the sake of confirmation.
.
is there something that i'm missing? or is there a difference in syntax in SQLite?
...
I also tried this:

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) ON Message.msgID = contact_m2m_message.messageID;


But this gives me the error:
no such column: contact_m2m_message.messageID ...  but it does exist in the definition.
.


.
The task i am striving to accomplish is to populate a list similar to Android Inbox view where each list item contains Contact Name, Number and a little Text from the latest message.


moreover, i'm testing queries using SQLite Studio v2.1.5
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 ;