Another SQL Puzzle

1

Table Schema

CREATE TABLE GUI ( 
win_id Integer NOT NULL DEFAULT 0,
ctrl_id Integer NOT NULL DEFAULT 0,
field   Text COLLATE NOCASE DEFAULT '',
data   Text COLLATE NOCASE DEFAULT '')

Sample Data

"win_id","ctrl_id","field","data"

1,1,"NAME","Button1"
1,1,"SUPER","PushButton"
1,2,"NAME","Button2"
1,2,"SUPER","PushButton"

SELECT name,super FROM GUI where win_ID=1

For some reason I can’t get my brain to grasp this… I know it is Unions and Joins

The desired result would be
Button1 , Pushbutton
Button2 , Pushbutton

2

Solved :slight_smile:

SELECT DISTINCT a.data AS nm, b.data AS super, c.data AS idx
  FROM (SELECT * FROM GUI WHERE field = "NAME")  a,
       (SELECT * FROM GUI WHERE field = "SUPER") b,
       (SELECT * FROM GUI WHERE field = "INDEX") c
 WHERE a.ctrl_id = b.ctrl_id
   AND a.win_id = b.win_id
   AND ( a.ctrl_id = c.ctrl_ID OR b.ctrl_id = c.ctrl_id)
   AND a.win_id = 1
3 
select a.data, b.data 
from gui a, gui b
where a.win_id = b.win_id
and a.ctrl_id = b.ctrl_id
and a.field = 'NAME' and b.field = 'SUPER';

But since the super is a property of a control, and all controls have one, why not just have a ‘SUPER’ property in the GUI table?
“win_id”,“ctrl_id”,“field”,“data”,“super”

4

It has to do with the source of the original data… would if I could but I can’t :slight_smile: