On Thu, 16 Jan 2014 09:47:58 -0500
Igor Tandetnik <[email protected]> wrote:
> On 1/16/2014 5:21 AM, Rob Golsteijn wrote:
> > SELECT * FROM C
> > LEFT JOIN A ON A.a*A.a + B.b*B.b = C.c*c.c
> > LEFT JOIN B ON A.a*A.a + B.b*B.b = C.c*c.c;
>
> I'm not sure how SQLite interprets this query. In any case, it
> doesn't make much sense. I suspect you are looking for something like
> this:
>
> SELECT * FROM A, B, C
> WHERE A.a*A.a + B.b*B.b = C.c*C.c;
That eliminates rows that don't meet the WHERE.
I think what's intended is
select a, b, c from C
left join (
select a, b
from A cross join B
where a <= b -- <== would eliminate duplicates
) as AB
on a*a + b*b = c*c;
which is easier to read if you ask me. I don't know if it executes
faster. For those who understand it, here's the EXPLAIN output:
addr opcode p1 p2 p3 p4 p5
----- ----- ----- ----- ----- ----- -----
0 Trace 0 0 0 00
1 Integer 21 1 0 00
2 Once 0 21 0 00
3 OpenEpheme 1 2 0 00
4 Goto 0 46 0 00
5 OpenRead 2 2 0 1 00
6 OpenRead 3 3 0 1 00
7 Rewind 2 19 0 00
8 Rewind 3 18 0 00
9 Column 2 0 2 00
10 Column 3 0 3 00
11 Gt 3 17 2 collseq(BI 6b
12 Column 2 0 4 00
13 Column 3 0 5 00
14 MakeRecord 4 2 3 00
15 NewRowid 1 2 0 00
16 Insert 1 3 2 08
17 Next 3 9 0 01
18 Next 2 8 0 01
19 Close 2 0 0 00
20 Close 3 0 0 00
21 Return 1 0 0 00
22 OpenRead 0 4 0 1 00
23 Rewind 0 44 0 00
24 Integer 0 6 0 00
25 Rewind 1 40 0 00
26 Column 1 0 9 00
27 Multiply 9 9 8 00
28 Column 1 1 11 00
29 Multiply 11 11 10 00
30 Add 10 8 7 00
31 Column 0 0 8 00
32 Multiply 8 8 10 00
33 Ne 10 39 7 6a
34 Integer 1 6 0 00
35 Column 1 0 13 00
36 Column 1 1 14 00
37 Column 0 0 15 00
38 ResultRow 13 3 0 00
39 Next 1 26 0 01
40 IfPos 6 43 0 00
41 NullRow 1 0 0 00
42 Goto 0 34 0 00
43 Next 0 24 0 01
44 Close 0 0 0 00
45 Halt 0 0 0 00
46 Transactio 0 0 0 00
47 VerifyCook 0 3 0 00
48 TableLock 0 2 0 A 00
49 TableLock 0 3 0 B 00
50 TableLock 0 4 0 C 00
51 Goto 0 5 0 00
--jkl
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
