Forum > Database
[SOLVED] Self select in SQLite?
paweld:
Maybe something like this:
--- Code: SQL [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---SELECT t.id, t.KEY, t.VALUE, t.col1, t.col2, t.col3, t.col4, t.col5 FROM TABLE t INNER JOIN (SELECT MIN(a.id) id FROM TABLE a INNER JOIN TABLE b ON (a.KEY=b.KEY) WHERE a.value<>b.VALUE GROUP BY a.KEY, a.VALUE) x ON (t.id=x.id) WHERE t.col1='color' AND (t.col3 LIKE '%text%' OR t.col5 IN (1, 3, 5)) ORDER BY t.KEY, t.VALUE
totya:
--- Quote from: paweld on February 24, 2023, 10:17:39 pm ---Maybe something like this:
--- Code: SQL [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---SELECT t.id, t.KEY, t.VALUE, t.col1, t.col2, t.col3, t.col4, t.col5 FROM TABLE t INNER JOIN (SELECT MIN(a.id) id FROM TABLE a INNER JOIN TABLE b ON (a.KEY=b.KEY) WHERE a.value<>b.VALUE GROUP BY a.KEY, a.VALUE) x ON (t.id=x.id) WHERE t.col1='color' AND (t.col3 LIKE '%text%' OR t.col5 IN (1, 3, 5)) ORDER BY t.KEY, t.VALUE
--- End quote ---
Thank you, it's a big help for me. Seems to me its works better, but not perfect yet. For example:
--- Code: SQL [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---SELECT t.ID, t.TYPE, t.Age, t.KeyName, t.KeyValueFROM main t INNER JOIN (SELECT MIN(a.ID) IDFROM main a INNER JOIN main b ON (a.KeyName=b.KeyName)WHERE a.KeyValue<>b.KeyValueGROUP BY a.KeyName, a.KeyValue) x ON (t.ID=x.ID)WHERE (t.TYPE='To') AND (t.Age="Last") ORDER BY t.KeyName, t.KeyValue;
In this query tAge has 3 possible value, but these command can find only two values of them (1 missing). In DB browser/browse data page I can found all values with above conditions.
Perhaps this line "a.KeyValue<>b.KeyValue" confuse logic.
Second, in the first line I can't access to a, b, or X.KeyValue (to see them).
Anyway, as I see, I will use more tables of the easy and exact select... but thank you again for this SQL Pro codes! O:-)
paweld:
--- Quote ---In this query tAge has 3 possible value, but these command can find only two values of them (1 missing)
--- End quote ---
happens attack because you wanted access only to records that differ, and so for the sample data:
idkeyvalueage1axc72adc123axc9
you will only get records with id 1 and 2.
if you want to receive all if the repetition occurs then the query must be like this:
--- Code: SQL [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---SELECT t.id, t.KEY, t.VALUE, t.col1, t.col2, t.col3, t.col4, t.col5 FROM TABLE t INNER JOIN (SELECT a.id FROM TABLE a INNER JOIN TABLE b ON (a.KEY=b.KEY) WHERE a.value<>b.VALUE GROUP BY a.id, a.KEY, a.VALUE) x ON (t.id=x.id) WHERE t.col1='color' AND (t.col3 LIKE '%text%' OR t.col5 IN (1, 3, 5)) ORDER BY t.KEY, t.VALUE
--- Quote ---Second, in the first line I can't access to a, b, or X.KeyValue (to see them)
--- End quote ---
there is no need to do so, as you are binding after the ID field then t.key=x.key and t.value=x.value
totya:
Thanky you, this code working better!
--- Quote from: paweld on February 25, 2023, 07:02:30 am ---there is no need to do so, as you are binding after the ID field then t.key=x.key and t.value=x.value
--- End quote ---
I need that, because I'd like to see the original vales of the selected type, but I understand, this is requires a new query code.
As I said already, compare data with own table is quite complicated (for me, perhaps not for you), therefore me I will use seperate tabes (with exactly same fields).
Thanks you again for these codes, I try to learn from them and make use of them.
totya:
--- Quote from: dogriz on February 24, 2023, 09:21:54 pm ---or something like this?
--- Code: SQL [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---SELECT t.KEY, t.VALUEFROM (SELECT KEY, VALUE, COUNT(KEY) FROM TEST_TABLE GROUP BY KEY, VALUE HAVING COUNT(KEY) > 1) t1JOIN TEST_TABLE t ON t.KEY = t1.KEYWHERE t.VALUE <> t1.VALUE
Shows only keys where values are different. (I'm not sure I understood the problem, don't laugh...)
--- End quote ---
Thank you, I read already your answer! I will not to laugh, beace my english is weak, so possible hard can be difficult to understand what I write.
Well, I tried your code, but the result is incorrectly, because I got about 25x more rows. But paweld also gave me 2 good working code.
Navigation
[0] Message Index
[#] Next page
[*] Previous page