SQLite subselect conundrum
This will give you all the data:
select *from data d1left join data d2 ON (d1.k1 = d2.k1) AND (d1.k2 = d2.k2) AND etc...where d1.element = "apple"Then you'll have to clean-up the results to exclude duplicates and do any other result-set preparation you may be interested in.
This is based on Ryan's idea, but the DISTINCT already eliminates duplicate keys, and the NATURAL join avoids the need to write out all the key comparisons:
SELECT d1.*FROM data AS d1NATURAL INNER JOIN (SELECT DISTINCT k1, k2, k3, k4 FROM data WHERE element = 'apple' ) AS d2(Natural joins can be dangerous because it is possible that the tables could be unintentionally changed later so that more columns have matching names, but here it's safe because d2's columns are explicitly listed.)