Forum > Database
[SOLVED] Self select in SQLite?
totya:
Hi!
Self-select in SQLite?
One table short verson is:
id : INTEGER PRIMARY KEY
key: TEXT
Value: TEXT
I'd like to see the records (with SELECT), where the key is the same, but the values are different with this (1) table.
Anyway Its easy it with two different tables. Thanks!
This problem solved by paweld in #2 and #7
Zvoni:
So you‘re looking for duplicate Keys, right?
paweld:
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 MIN(a.id) id, a.KEY, a.VALUE FROM TABLE a INNER JOIN TABLE b ON (a.KEY=b.KEY) WHERE a.value<>b.VALUE GROUP BY a.KEY, a.VALUE ORDER BY a.KEY, a VALUE
dogriz:
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...)
totya:
--- Quote from: paweld on February 24, 2023, 07:17:44 pm ---Something like this:
--- End quote ---
Thank you, seems to me its's working! :)
But if I add more conditions to the SQL command (in the real table has more field), the b.Value isn't perfect, because as I see the additional conditions are ignored.
I tried add conditions (AND) add after the "ON (a.KEY=b.KEY)" or after the "WHERE a.value<>b.VALUE"
Do you have any idea to add workable AND conditions to the your SQL commands?
Navigation
[0] Message Index
[#] Next page