I’m obviously missing something … but have not yet figured out what it is
"SELECT a.field1,a.field2,ifNull(CASE b.field3 WHEN '' THEN '*' ELSE b.field3,'?') as field3 " + _
" FROM table1 a" + _
" LEFT JOIN table2 b" + _
" ON a.field1=b.field1'" Simple left join of two tables…
Table 2 does NOT always have a matching value to Table 1… if it DOESN’t, I need Field 3 to be ‘?’
If there is a match (and there can be more than one, and I want them all), it is possible that Field 3 is ‘’ (NOT a null)
but I need THAT to be in the output a “*”
So Field 3 will have 3 types of values
- The actual value of Field3 in table 2 if it exists and is NOT ‘’ (again… not null, and there are no nulls)
- a value of “*”, which indicates there is a corresponding record in table 2, just no specifed value for field3
- a value of “?” which indicates there is NOT a corresponding match in table 2
SQLite indicates "Syntax error near ‘,’
which I assue is the one right before the IFNULL
Note, for those of you thinking, “Hey he said there were no nulls, yet he uses IFNULL? WTF?”
Remember on a left join, it keeps everything from the “right” table (table1 in this case), and attempts to join things from the “left” table (table2), returning a NULL if there is no match. So my DATA has no nulls, but the join process will create some