[SOLVED] Self select in SQLite?
lazarus / 63-Database

----------------------------------------------------------------------
totya · 2023-02-24 16:25

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 · 2023-02-24 17:57

So you‘re looking for duplicate Keys, right?

----------------------------------------------------------------------
paweld · 2023-02-24 18:17

Something like this: 
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 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 · 2023-02-24 20:21

or something like this?
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 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 · 2023-02-24 20:25

Quote from: paweld on February 24, 2023, 07:17:44 pmSomething like
this:
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?

----------------------------------------------------------------------
paweld · 2023-02-24 21:17

Maybe something like this:
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 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 · 2023-02-24 22:22

Quote from: paweld on February 24, 2023, 10:17:39 pmMaybe something
like this:
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 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
Thank you, it's a big help for me. Seems to me its works better, but
not perfect yet. For example:

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 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!

----------------------------------------------------------------------
paweld · 2023-02-25 06:02

QuoteIn this query tAge has 3 possible value, but these command can
find only two values of them (1 missing) 
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  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 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  
QuoteSecond, in the first line I can't access to a, b, or X.KeyValue
(to see them) 
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 · 2023-02-25 09:34

Thanky you, this code working better!

Quote from: paweld on February 25, 2023, 07:02:30 amthere is no need
to do so, as you are binding after the ID field then t.key=x.key and
t.value=x.value
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 · 2023-02-25 09:45

Quote from: dogriz on February 24, 2023, 09:21:54 pmor something like
this?
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 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...)

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.

----------------------------------------------------------------------
totya · 2023-02-25 13:19

As I read the sql informations the best way to avoid multiple tables
or to very complex query: VIEW.