in reply to Re^2: OT - SQL choosing a schema for index tables
in thread OT - SQL choosing a schema for index tables

SELECT id from tree_field_index 
  WHERE ( (field='usage' AND name='cider') 
  OR      (field='usage' AND name='juicer') ) 
  AND     (field='flowering' AND name='spring') 
  AND     (field='ripe' AND name='autumn')

[download]

This won't work. The select statement gets applied to each record in turn.You can't have a single record where field='usage' AND field='flowering' so the result will be nothing.

Which database are you using, and how many records roughly are you dealing with ?

poj

Replies are listed 'Best First'.
Re^4: OT - SQL choosing a schema for index tables
by bangor (Monk) on Aug 28, 2015 at 13:19 UTC
    Thank you poj, you are of course correct, and my Option 2 will not work as I had hoped - you have saved me some time there. I am using MySQL and in this table there are about 200 rows.
[reply]

      I should point out that you could make it work but it would be rather complicated !.
      Alternatively you could retain the multiple values and use REGEXP in the SELECT . For example

      Read more... (2 kB)

      Or you could select the records using the single value fields where possible and then filter them with a perl routine using regular expressions.

      poj
[reply]
[d/l]
[select]
        This is something like how the current system (in PHP) works, but it uses LIKE in the SELECT and sometimes returns incorrect results. I didn't know you could use REGEXP and I can see how it would fix that problem - thank you.

        Your second suggestion could also work for me, but as you imply, it is not guaranteed that the query will contain a value for the single-value fields.

[reply]

In Section Seekers of Perl Wisdom