[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  [Select][+][-]

1. SELECT MIN(a.id) id, a.KEY, a.VALUE
2. FROM TABLE a INNER JOIN TABLE b ON (a.KEY=b.KEY)
3. WHERE a.value<>b.VALUE
4. GROUP BY a.KEY, a.VALUE
5. ORDER BY a.KEY, a VALUE
6.  

[dogriz]

or something like this?

Code: SQL  [Select][+][-]

1. SELECT t.KEY, t.VALUE
2. FROM (SELECT KEY, VALUE, COUNT(KEY) FROM TEST_TABLE GROUP BY KEY, VALUE HAVING COUNT(KEY) > 1) t1
3. JOIN TEST_TABLE t ON t.KEY = t1.KEY
4. WHERE t.VALUE <> t1.VALUE
5.  

Shows only keys where values are different. (I'm not sure I understood the problem, don't laugh...)

[totya]

Something 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]

Maybe something like this:

Code: SQL  [Select][+][-]

1. SELECT t.id, t.KEY, t.VALUE, t.col1, t.col2, t.col3, t.col4, t.col5  
2. FROM TABLE t INNER JOIN (SELECT MIN(a.id) id  
3. FROM TABLE a INNER JOIN TABLE b ON (a.KEY=b.KEY)  
4. WHERE a.value<>b.VALUE  
5. GROUP BY a.KEY, a.VALUE) x ON (t.id=x.id)  
6. WHERE t.col1='color' AND (t.col3 LIKE '%text%' OR t.col5 IN (1, 3, 5))          
7. ORDER BY t.KEY, t.VALUE

[totya]

Maybe something like this:

Code: SQL  [Select][+][-]

1. SELECT t.id, t.KEY, t.VALUE, t.col1, t.col2, t.col3, t.col4, t.col5  
2. FROM TABLE t INNER JOIN (SELECT MIN(a.id) id  
3. FROM TABLE a INNER JOIN TABLE b ON (a.KEY=b.KEY)  
4. WHERE a.value<>b.VALUE  
5. GROUP BY a.KEY, a.VALUE) x ON (t.id=x.id)  
6. WHERE t.col1='color' AND (t.col3 LIKE '%text%' OR t.col5 IN (1, 3, 5))          
7. 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][+][-]

1. SELECT t.ID, t.TYPE, t.Age, t.KeyName, t.KeyValue
2. FROM main t
3. INNER JOIN (SELECT MIN(a.ID) ID
4. FROM main a
5. INNER JOIN main b ON (a.KeyName=b.KeyName)
6. WHERE a.KeyValue<>b.KeyValue
7. GROUP BY a.KeyName, a.KeyValue) x ON (t.ID=x.ID)
8. WHERE (t.TYPE='To') AND (t.Age="Last") ORDER BY t.KeyName, t.KeyValue;
9.  

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]

In 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: 

id	key	value	age
1	a	xc	7
2	a	dc	12
3	a	xc	9

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][+][-]

1. SELECT t.id, t.KEY, t.VALUE, t.col1, t.col2, t.col3, t.col4, t.col5  
2. FROM TABLE t INNER JOIN (SELECT a.id
3.   FROM TABLE a INNER JOIN TABLE b ON (a.KEY=b.KEY)  
4.   WHERE a.value<>b.VALUE  
5.   GROUP BY a.id, a.KEY, a.VALUE) x ON (t.id=x.id)  
6. WHERE t.col1='color' AND (t.col3 LIKE '%text%' OR t.col5 IN (1, 3, 5))          
7. ORDER BY t.KEY, t.VALUE

 

Second, 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]

Thanky you, this code working better!

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

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]

or something like this?

Code: SQL  [Select][+][-]

1. SELECT t.KEY, t.VALUE
2. FROM (SELECT KEY, VALUE, COUNT(KEY) FROM TEST_TABLE GROUP BY KEY, VALUE HAVING COUNT(KEY) > 1) t1
3. JOIN TEST_TABLE t ON t.KEY = t1.KEY
4. WHERE t.VALUE <> t1.VALUE
5.  

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]

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