CODEWITHSUNDEEP
sqliteselectambiguous
HeatherLeigh
HeatherLeigh

Reputation: 71

Sqlite ambiguous column name that isn't ambiguous, includes table name

I have the following SELECT statement for sqlite that is coming back with Error: ambiguous column name: msgs.msgcontent1 however that column name is not ambiguous! Can anyone help me figure out what I am missing here?

SELECT 
  phone_lookup.normalized_number AS number , 
  contacts.display_name AS name , 
  msgs.msgcontent1 
FROM 
  contacts2.contacts , 
  temp.msgs 
  INNER JOIN contacts2.phone_lookup ON ( contacts.name_raw_contact_id = phone_lookup.raw_contact_id ) 
  INNER JOIN temp.msgs ON ( contacts.phone_lookup.normalized_number = msgs.sender ) ;

The following the is statement I used to create the table that contains the troublesome column:

CREATE TEMP TABLE msgs 
AS SELECT 
  PART."_ID" , 
  ADDR."_ID" , 
  DATETIME( SUBSTR( COALESCE( PDU."DATE" , SMS."DATE" ) , 1 , 10 ) , 'localtime' , 'unixepoch' ) AS dt , 
  COALESCE( PDU.READ , SMS.READ ) AS read , 
  COALESCE( SMS.ADDRESS , ADDR.ADDRESS ) AS sender , 
  COALESCE( SMS.BODY , COALESCE( PART.TEXT , PART."_DATA" ) ) AS msgcontent1 
FROM 
  mmssms.PDU 
  LEFT OUTER JOIN mmssms.SMS ON ( PDU."DATE" = SMS."DATE" ) 
  LEFT OUTER JOIN mmssms.PART ON ( PART.MID = PDU."_ID" ) 
  LEFT OUTER JOIN mmssms.ADDR ON ( ADDR.MSG_ID = PDU."_ID" ) 
WHERE 
  COALESCE( SMS.BODY , COALESCE( PART.TEXT , PART."_DATA" ) ) IS NOT NULL AND ADDR."_ID" IN 
    ( 
      SELECT 
        MIN( ADDR."_ID" ) 
      FROM 
        ADDR 
      GROUP BY 
        ADDR.MSG_ID 
      ORDER BY 
        ADDR."_ID" 
    ) AND PART."_ID" IN 
    ( 
      SELECT 
        MAX( PART."_ID" ) 
      FROM 
        mmssms.PART 
      GROUP BY 
        PART.MID 
      ORDER BY 
        PART."_ID" 
    ) 
UNION ALL 
SELECT 
  PART."_ID" , 
  ADDR."_ID" , 
  DATETIME( SUBSTR( COALESCE( PDU."DATE" , SMS."DATE" ) , 1 , 10 ) , 'localtime' , 'unixepoch' ) AS dt , 
  COALESCE( PDU."READ" , SMS."READ" ) AS read , 
  COALESCE( SMS."ADDRESS" , ADDR."ADDRESS" ) AS sender , 
  COALESCE( SMS."BODY" , COALESCE( PART."TEXT" , PART."_DATA" ) ) AS msgcontent2 
FROM 
  mmssms.SMS 
  LEFT OUTER JOIN mmssms.PDU ON ( PDU."DATE" = SMS."DATE" ) 
  LEFT OUTER JOIN mmssms.PART ON ( PART."MID" = PDU."_ID" ) 
  LEFT OUTER JOIN mmssms.ADDR ON ( ADDR."MSG_ID" = PDU."_ID" ) 
WHERE 
  COALESCE( SMS."BODY" , COALESCE( PART."TEXT" , PART."_DATA" ) ) IS NOT NULL 
ORDER BY 
  DATETIME( SUBSTR( COALESCE( PDU."DATE" , SMS."DATE" ) , 1 , 10 ) , 'localtime' , 'unixepoch' ) DESC , 
  ADDR."_ID" ASC ;

And finally, although I don't believe it's relevant to the issue, I begin the sqlite commands with the following pragma and attach statements to make sure I have access to everything:

PRAGMA temp_store = MEMORY ;
ATTACH '/data/data/com.android.providers.contacts/databases/contacts2.db' AS contacts2 ;
ATTACH '/data/data/com.android.providers.telephony/databases/mmssms.db' AS mmssms ;

Upvotes: 0

Views: 1420

Answers (1)

Rimas
Rimas

Reputation: 6024

It look likes temp.msgs after FROM is not required as it already is in INNER JOIN clause. So this query must work:

SELECT 
  phone_lookup.normalized_number AS number , 
  contacts.display_name AS name , 
  msgs.msgcontent1 
FROM 
  contacts2.contacts
  INNER JOIN contacts2.phone_lookup ON ( contacts.name_raw_contact_id = phone_lookup.raw_contact_id ) 
  INNER JOIN temp.msgs ON ( contacts.phone_lookup.normalized_number = msgs.sender ) ;

If you need to specify the same table in two places (FROM and JOIN clauses) then you must use distinct aliases for each and refer to columns using these aliases.

More info: SQLite Query Language: SELECT

Upvotes: 1

Related Questions