0

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

  • 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 ; – ypercubeᵀᴹ Oct 12 '14 at 12:10
  • but tell me, why Sqlite isn't working on nested query? it seems it doesn't support it? – Mashhood Oct 12 '14 at 12:28
  • 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? – ypercubeᵀᴹ Oct 12 '14 at 12:32
  • no, i didn't try that without parenthesis before your answer. now it is working super fine.. thnx dear.. <3 – Mashhood Oct 12 '14 at 12:37
0

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 ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.