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 ;
Best Answer
You would have to obtain the Source Code for SQLLite, change the value of SQLITE_MAX_COLUMN, recompile the code and distrbute the resulting executable.
However, more than 2000 columns in a single table sounds wrong to me. Do you have lots of repeated fields in each row? That's poor Relational design.
If necessary, create a second table with the same primary key as the first and add your additional fields into that. You would have to issue two select statements to retrieve the values, though, because SQLITE_MAX_COLUMNS also applies to select statements, so you can't just join the two tables together.