CTRL_ID is a number
PROP_NAME is various property names
PROP_VALUE is the value of that property
I need to extract the values for PROP_NAME = “WIDTH” or “HEIGHT”
and create a record that looks like
CTRL_ID, WIDTH, HEIGHT
I for some reason can’t get JUST the rows I need
select a.ctrl_id,a.prop_default as width, b.prop_default as height,a.prop_name as x,b.prop_name as c
from properties a
left join properties b
on a.ctrl_id = b.ctrl_id
this gives EVERY combination of PROP_NAME
I’ve tried
adding "WHERE a.prop_name=‘WIDTH’ and b.prop_name=“HEIGHT”
and various other combinations… and get nothing then
I know this isn’t rocket science… .but having big time brain fade here
Turns out I had to use LIKE “prop_name like ‘width%’”
don’t know why since there is no zombies or whitespace involved
select a.ctrl_id,a.prop_default as width, b.prop_default as height
from properties a
left join properties b
on a.ctrl_id = b.ctrl_id
where a.prop_name like "width%" and b.prop_name like "height%"
Would this (or it’s equivalent depending on DB) work:
select a.ctrl_id,a.prop_default as width, b.prop_default as height
from properties a
left join properties b
on a.ctrl_id = b.ctrl_id
where lower(a.prop_name) = "width%" and lower(b.prop_name) = "height%"
Are you sure there are no gremlins in your data? Assuming this is SQLite, the NOCASE coalition only works on ASCII letters:
NOCASE - Similar to binary, except that it uses sqlite3_strnicmp() for the comparison. Hence the 26 upper case characters of ASCII are folded to their lower case equivalents before the comparison is performed. Note that only ASCII characters are case folded. SQLite does not attempt to do full UTF case folding due to the size of the tables required. Also note that any U+0000 characters in the string are considered string terminators for comparison purposes.
Can you use a hex editor and look at the actual data in the database? Perhaps there is an EOL or other character that disqualifies the =“width” or =“height” test.