SQLite - No Such Column TABLE.ROWID

Question

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

• ypercubeᵀᴹ: Can you try this?: SELECT Message.messageContent, Message.messageDateTime FROM Message INNER JOIN contact_m2m_message ON Message.msgID = contact_m2m_message.messageID INNER JOIN Contact ON Contact.contactID = contact_m2m_message.contactID ;
• Mashhood: but tell me, why Sqlite isn't working on nested query? it seems it doesn't support it?
• ypercubeᵀᴹ: Can you create a sample with the CREATE TABLE statements in sqlfiddle.com so we can test? And did you try the first query without the parentheses?
• Mashhood: no, i didn't try that without parenthesis before your answer. now it is working super fine.. thnx dear.. <3

Answer 1

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
 ) --<-------------------------------------- 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 ;