Re: [sqlite] Nested Inner Join Help

[John Machin]

On 3/07/2009 7:08 AM, Ed Hawke wrote:
> 
> Out of interest, would I be able to use binding on the run-time defined 
> fields?
> 
> If I wanted to use:
> 
> select * from A
> join B b1 on (A.Column3 = b1.ID)
> join C c1 on (b1.Column1 = c1.ID)
> join D d1 on (b1.Column2 = d1.ID)
> 
> join B b2 on (A.Column4 = b2.ID)
> join C c2 on (b2.Column1 = c2.ID)
> join D d2 on (b2.Column2 = d2.ID);
> where d2.ID = ?
> 
> Would that work?

If we had an explicit literal, e.g. "where d2.ID = 1234", would that 
work? Of course it would work.

Here's the scoop: you can have a binding parameter anywhere you can have 
a literal. It's that simple.

HTH,
John
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Nested Inner Join Help

[Swithun Crowe]

Hello

EH Do the "a2-style" (for want of a better way of defining them) names 
EH exist outside the SQL statement which defines them?

Tables and columns have fixed names, which you define when you write your 
database schema, e.g.

CREATE TABLE myTable (id INT, value TEXT);

But you can give these names aliases in your SQL statements: e.g.

SELECT t1.id AS id1, t1.value AS val1
FROM myTable AS t1
INNER JOIN myTable AS t2
ON t1.id = t2.id;

These aliases only exist in the statements that define what they are 
aliasing. You can't reuse them in other statements without again 
specifying what they are aliasing.

If I've understood you, then what you should read up on is aliases.

I hope this helps.

Swithun.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Nested Inner Join Help

[Igor Tandetnik]

Ed Hawke wrote:
> Fine.
>
> Do the "a2-style" (for want of a better way of defining them) names
> exist outside the SQL statement which defines them?

Why does it matter?

> i.e. if I execute the example statement that you gave me, then later
> execute a statement which references c2 will that work?

No. But again, why is this significant? What additional insight does it 
give you?

Anyway, this discussion has veered off-topic and, frankly, become rather 
boring. I think I'm done with it.

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Nested Inner Join Help

[Ed Hawke]

Fine.

Do the "a2-style" (for want of a better way of defining them) names 
exist outside the SQL statement which defines them?

i.e. if I execute the example statement that you gave me, then later 
execute a statement which references c2 will that work? Will other 
statements in the same transaction be able to reference c2, or does the 
reference only persist in the statement in which c2 was defined?

Ed

Igor Tandetnik wrote:
> Ed Hawke wrote:
>   
>> All I meant was that in a database you have defined tables with
>> defined column names. These are defined before any SQL statements are
>> executed and therefore I would classify them as pre-defined.
>>
>> Contrast this with the "labels" applied to tables to create a separate
>> reference to them within an SQL statement (e.g. a2 in your example).
>> Before the SQL statement creating these references is executed then
>> they will not be "recognised" by anything (i.e. any references to
>> them in other statements will throw up an error). Therefore these are
>> only defined when the SQL Statement that defines them is run, and
>> therefore I would classify them as run-time defined.
>> 
>
> I don't see how this fine distinction is interesting, nor why it's worth 
> making. While "predefined" column names may exist, in some philosophical 
> sense, outside the context of a SQL statement, the only way for you to 
> use them or refer to them is by mentioning them in a SQL statement, at 
> which point all distinction between "predefined" and "dynamically 
> generated" names evaporates.
>
> Igor Tandetnik
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Nested Inner Join Help

[Igor Tandetnik]

Ed Hawke wrote:
> All I meant was that in a database you have defined tables with
> defined column names. These are defined before any SQL statements are
> executed and therefore I would classify them as pre-defined.
>
> Contrast this with the "labels" applied to tables to create a separate
> reference to them within an SQL statement (e.g. a2 in your example).
> Before the SQL statement creating these references is executed then
> they will not be "recognised" by anything (i.e. any references to
> them in other statements will throw up an error). Therefore these are
> only defined when the SQL Statement that defines them is run, and
> therefore I would classify them as run-time defined.

I don't see how this fine distinction is interesting, nor why it's worth 
making. While "predefined" column names may exist, in some philosophical 
sense, outside the context of a SQL statement, the only way for you to 
use them or refer to them is by mentioning them in a SQL statement, at 
which point all distinction between "predefined" and "dynamically 
generated" names evaporates.

Igor Tandetnik



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Nested Inner Join Help

[Ed Hawke]

All I meant was that in a database you have defined tables with defined 
column names. These are defined before any SQL statements are executed 
and therefore I would classify them as pre-defined.

Contrast this with the "labels" applied to tables to create a separate 
reference to them within an SQL statement (e.g. a2 in your example). 
Before the SQL statement creating these references is executed then they 
will not be "recognised" by anything (i.e. any references to them in 
other statements will throw up an error). Therefore these are only 
defined when the SQL Statement that defines them is run, and therefore I 
would classify them as run-time defined.

I am aware as I said that these are probably not the correct SQL 
definitions, they were self-applied definitions to help make clear what 
I was referring to. I can only apologise that it made it more complicated.

Ed

Igor Tandetnik wrote:
> Ed Hawke wrote:
>   
>> By run-time defined fields I meant column names that SQL would not
>> recognise until the query was executed
>> 
>
> I don't get the distinction. Could you give an example of column names 
> that SQL would somehow "recognize" before a query is executed? What do 
> you mean by "recognize" here, anyway?
>
> Igor Tandetnik
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Nested Inner Join Help

[Kees Nuyt]

On Fri, 03 Jul 2009 00:53:12 +0100, Ed Hawke
 wrote:

>Thank you again Igor.
>
>By run-time defined fields I meant column names that SQL would not 
>recognise until the query was executed, and therefore are only defined 
>when the statement is "run". I am aware that this is probably not the 
>correct terminology.

You can only bind values to predetermined columns. 
The table names and column names in a sqlite_prepared
statement are static and can't be replaced by placeholders.

In other words, there are sqlite3_bind_*() functions for all
types of values but nothing like sqlite3_bind_tablename() or
sqlite3_bind_columnname().

http://www.sqlite.org/c3ref/funclist.html
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Nested Inner Join Help

[Igor Tandetnik]

Ed Hawke wrote:
> By run-time defined fields I meant column names that SQL would not
> recognise until the query was executed

I don't get the distinction. Could you give an example of column names 
that SQL would somehow "recognize" before a query is executed? What do 
you mean by "recognize" here, anyway?

Igor Tandetnik



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Nested Inner Join Help

[Ed Hawke]

Thank you again Igor.

By run-time defined fields I meant column names that SQL would not 
recognise until the query was executed, and therefore are only defined 
when the statement is "run". I am aware that this is probably not the 
correct terminology.

Ed

Igor Tandetnik wrote:
> Ed Hawke
>  wrote:
>   
>> Out of interest, would I be able to use binding on the run-time
>> defined fields?
>> 
>
> What's "run-time defined fields"? I'm not familiar with the term.
>
>   
>> If I wanted to use:
>>
>> select * from A
>>join B b1 on (A.Column3 = b1.ID)
>>join C c1 on (b1.Column1 = c1.ID)
>>join D d1 on (b1.Column2 = d1.ID)
>>
>>join B b2 on (A.Column4 = b2.ID)
>>join C c2 on (b2.Column1 = c2.ID)
>>join D d2 on (b2.Column2 = d2.ID);
>> where d2.ID = ?
>> 
>
> Remove the semicolon before WHERE. Otherwise, I don't see anything wrong 
> with this.
>
> Igor Tandetnik 
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Nested Inner Join Help

[Igor Tandetnik]

Ed Hawke
 wrote:
> Out of interest, would I be able to use binding on the run-time
> defined fields?

What's "run-time defined fields"? I'm not familiar with the term.

> If I wanted to use:
>
> select * from A
>join B b1 on (A.Column3 = b1.ID)
>join C c1 on (b1.Column1 = c1.ID)
>join D d1 on (b1.Column2 = d1.ID)
>
>join B b2 on (A.Column4 = b2.ID)
>join C c2 on (b2.Column1 = c2.ID)
>join D d2 on (b2.Column2 = d2.ID);
> where d2.ID = ?

Remove the semicolon before WHERE. Otherwise, I don't see anything wrong 
with this.

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Nested Inner Join Help

[Ed Hawke]

Thank you very much for this Igor.

Out of interest, would I be able to use binding on the run-time defined 
fields?

If I wanted to use:

select * from A
join B b1 on (A.Column3 = b1.ID)
join C c1 on (b1.Column1 = c1.ID)
join D d1 on (b1.Column2 = d1.ID)

join B b2 on (A.Column4 = b2.ID)
join C c2 on (b2.Column1 = c2.ID)
join D d2 on (b2.Column2 = d2.ID);
where d2.ID = ?

Would that work?

Regards,

Ed


Igor Tandetnik wrote:
> Ed Hawke
>  wrote:
>   
>> To clarify this (I hope) if my table set-up is:
>>
>> Table A:
>> IDColumn1Column2Column3Column4
>> 112345678921  23
>> 216321587622  21
>> 331456910822  24
>>
>> Table B:
>> IDColumn1Column2
>> 2131   42
>> 2231   41
>> 2333   43
>> 2432   41
>>
>> Table C:
>> IDColumn1
>> 31 Name1
>> 32 Name2
>> 33 Name3
>>
>> Table D:
>> IDColumn1
>> 41   Info1
>> 42   Info2
>> 43   Info3
>>
>> How would I select A.ID, A.Column1, A.Column2, C.Column1, D.Column1,
>> C.Column1, D.Column1 where the first set of info from C and D is based
>> on the ID contained in A.Column3, and the second set on the ID in
>> A.Column4?
>> 
>
> select * from A
> join B b1 on (A.Column3 = b1.ID)
> join C c1 on (b1.Column1 = c1.ID)
> join D d1 on (b1.Column2 = d1.ID)
>
> join B b2 on (A.Column4 = b2.ID)
> join C c2 on (b2.Column1 = c2.ID)
> join D d2 on (b2.Column2 = d2.ID);
>
> Igor Tandetnik 
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Nested Inner Join Help

[Igor Tandetnik]

Ed Hawke
 wrote:
> To clarify this (I hope) if my table set-up is:
>
> Table A:
> IDColumn1Column2Column3Column4
> 112345678921  23
> 216321587622  21
> 331456910822  24
>
> Table B:
> IDColumn1Column2
> 2131   42
> 2231   41
> 2333   43
> 2432   41
>
> Table C:
> IDColumn1
> 31 Name1
> 32 Name2
> 33 Name3
>
> Table D:
> IDColumn1
> 41   Info1
> 42   Info2
> 43   Info3
>
> How would I select A.ID, A.Column1, A.Column2, C.Column1, D.Column1,
> C.Column1, D.Column1 where the first set of info from C and D is based
> on the ID contained in A.Column3, and the second set on the ID in
> A.Column4?

select * from A
join B b1 on (A.Column3 = b1.ID)
join C c1 on (b1.Column1 = c1.ID)
join D d1 on (b1.Column2 = d1.ID)

join B b2 on (A.Column4 = b2.ID)
join C c2 on (b2.Column1 = c2.ID)
join D d2 on (b2.Column2 = d2.ID);

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Nested Inner Join Help

[Ed Hawke]

Thank you both for your help.

That works, but what happens if I want to do a more complex query. If 
Table B contains references to tables C and D then I can just extend 
your example to select information from D as well as C. However if I 
have multiple Columns in A that are occurrences of B.ID how would I 
create a statement to select the information from C and D for each of these?

To clarify this (I hope) if my table set-up is:

Table A:
IDColumn1Column2Column3Column4
112345678921  23
216321587622  21
331456910822  24

Table B:
IDColumn1Column2
2131   42
2231   41
2333   43
2432   41

Table C:
IDColumn1
31 Name1
32 Name2
33 Name3

Table D:
IDColumn1
41   Info1
42   Info2
43   Info3

How would I select A.ID, A.Column1, A.Column2, C.Column1, D.Column1, 
C.Column1, D.Column1 where the first set of info from C and D is based 
on the ID contained in A.Column3, and the second set on the ID in A.Column4?

Sorry, I know this is massively more complicated, but that was why I was 
attempting to use nested Inner Joins, although I'm not necessarily sure 
they would have helped in this situation anyway.

Regards,

Ed

Adam DeVita wrote:
> why not use:
>
> SELECT A.ID , A.Column1, A.Column2, B.Column1, C.Column1
> FROM A
> INNER JOIN B ON A.Column3 = B.ID 
> INNER JOIN C ON B.Column2 = C.ID 
>
> ?
>
> On Thu, Jul 2, 2009 at 2:53 PM, Ed Hawke 
>  > wrote:
>
> Hi all,
>
> I'm having problems getting nested inner joins to work with SQLite. As
> far as I can tell from various resources the correct way of joining
> multiple tables is this:
>
> SELECT A.ID , A.Column1, A.Column2, B.Column1,
> C.Column1 FROM A INNER
> JOIN B (INNER JOIN C ON B.Column2 = C.ID ) ON
> A.Column3 = B.ID 
>
> However depending upon where I put the parentheses I get various
> different errors from the viewer I use (SQLite Manager for Firefox). A
> normal Inner Join without the nesting works fine.
>
> Can anyone tell me what I'm doing wrong?
>
> Regards,
>
> Ed
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org 
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>
> -- 
> VerifEye Technologies Inc.
> 905-948-0015x245
> 7100 Warden Ave, Unit 3
> Markham ON, L3R 8B5
> Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Nested Inner Join Help

[Adam DeVita]

why not use:

SELECT A.ID , A.Column1, A.Column2, B.Column1, C.Column1
FROM A
INNER JOIN B ON A.Column3 = B.ID 
INNER JOIN C ON B.Column2 = C.ID 

?

On Thu, Jul 2, 2009 at 2:53 PM, Ed Hawke <
edward.ha...@hawkeyeinnovations.co.uk> wrote:

> Hi all,
>
> I'm having problems getting nested inner joins to work with SQLite. As
> far as I can tell from various resources the correct way of joining
> multiple tables is this:
>
> SELECT A.ID, A.Column1, A.Column2, B.Column1, C.Column1 FROM A INNER
> JOIN B (INNER JOIN C ON B.Column2 = C.ID) ON A.Column3 = B.ID
>
> However depending upon where I put the parentheses I get various
> different errors from the viewer I use (SQLite Manager for Firefox). A
> normal Inner Join without the nesting works fine.
>
> Can anyone tell me what I'm doing wrong?
>
> Regards,
>
> Ed
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
7100 Warden Ave, Unit 3
Markham ON, L3R 8B5
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Nested Inner Join Help

[Pavel Ivanov]

I believe you need this:

SELECT A.ID, A.Column1, A.Column2, B.Column1, C.Column1
FROM A INNER JOIN B ON A.Column3 = B.ID
  INNER JOIN C ON B.Column2 = C.ID

Pavel

On Thu, Jul 2, 2009 at 2:53 PM, Ed
Hawke wrote:
> Hi all,
>
> I'm having problems getting nested inner joins to work with SQLite. As
> far as I can tell from various resources the correct way of joining
> multiple tables is this:
>
> SELECT A.ID, A.Column1, A.Column2, B.Column1, C.Column1 FROM A INNER
> JOIN B (INNER JOIN C ON B.Column2 = C.ID) ON A.Column3 = B.ID
>
> However depending upon where I put the parentheses I get various
> different errors from the viewer I use (SQLite Manager for Firefox). A
> normal Inner Join without the nesting works fine.
>
> Can anyone tell me what I'm doing wrong?
>
> Regards,
>
> Ed
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Nested Inner Join Help

[Ed Hawke]

Hi all,

I'm having problems getting nested inner joins to work with SQLite. As 
far as I can tell from various resources the correct way of joining 
multiple tables is this:

SELECT A.ID, A.Column1, A.Column2, B.Column1, C.Column1 FROM A INNER 
JOIN B (INNER JOIN C ON B.Column2 = C.ID) ON A.Column3 = B.ID

However depending upon where I put the parentheses I get various 
different errors from the viewer I use (SQLite Manager for Firefox). A 
normal Inner Join without the nesting works fine.

Can anyone tell me what I'm doing wrong?

Regards,

Ed
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users