[sqlite] are nested joins possible ?

[Stef Mientki]

I thought this would work,

SELECT *
 FROM Patient_Text
 INNER JOIN
 (
SELECT *
 FROM Patient
 INNER JOIN Opnamen
 ON Patient.PatNr = Opnamen.PatNr
 )
 ON Patient.PatNr = Patient_Text.PatNr
 
But I get an error on the second use of Patient.PatNr.
Is there a way to get such a nested statement working ??

I'm looking for some construct that can used to generated code
from a visual design, through recursion of the links,
so it must be simple.

thanks,

--
cheers,
Stef Mientki
http://pic.flappie.nl


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] are nested joins possible ?

[Dennis Cote]

Stef Mientki wrote:

I thought this would work,

SELECT *
 FROM Patient_Text
 INNER JOIN
 (
SELECT *
 FROM Patient
 INNER JOIN Opnamen
 ON Patient.PatNr = Opnamen.PatNr
 )
 ON Patient.PatNr = Patient_Text.PatNr
 
But I get an error on the second use of Patient.PatNr.
Is there a way to get such a nested statement working ??

I'm looking for some construct that can used to generated code
from a visual design, through recursion of the links,
so it must be simple.

Stef,

The subselect produces an unnamed table as its result. If you want to 
refer to its columns you must drop the table name or add an alias to the 
subselect and use that name.


   SELECT *
FROM Patient_Text
INNER JOIN
(
   SELECT *
FROM Patient
INNER JOIN Opnamen
ON Patient.PatNr = Opnamen.PatNr
)
ON PatNr = Patient_Text.PatNr
   
   SELECT *

FROM Patient_Text
INNER JOIN
(
   SELECT *
FROM Patient
INNER JOIN Opnamen
ON Patient.PatNr = Opnamen.PatNr
) AS Pat
ON Pat.PatNr = Patient_Text.PatNr

It seems to me that  you should be able to do this without a subselect 
though.


   SELECT *
FROM Patient_Text
INNER JOIN Patient ON Patient.PatNr = Patient_Text.PatNr
INNER JOIN Opnamen ON Patient.PatNr = Opnamen.PatNr

The links in your visual tool are just joins between the tables, no need 
for the subselects.


HTH
Dennis Cote


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] are nested joins possible ?

[Samuel R. Neff]

give the nested select an alias so you can reference it:

SELECT *
  FROM Patient_Text
  INNER JOIN
  (
SELECT *
  FROM Patient
  INNER JOIN Opnamen
  ON Patient.PatNr = Opnamen.PatNr
  ) N
  ON N.PatNr = Patient_Text.PatNr 

But if this is your actual query then I would suggest either joining all
three tables or using a where clause with IN instead of a nested select.

HTH,

Sam


---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] are nested joins possible ?

[Stef Mientki]

thanks guys,
for the fast and different responses,
I really appreciate that.
... now I need a couple hours to study all your ideas.

--
cheers,
Stef Mientki
http://pic.flappie.nl


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] are nested joins possible ?

[Stef Mientki]

Stef Mientki wrote:

thanks guys,
for the fast and different responses,
I really appreciate that.
... now I need a couple hours to study all your ideas.


After studying all your suggestions, I must conclude
that my knowledge of SQL is too low to create a real fool proof solution.
For the moment I've it working for simple visual designs, with the 
following algorithm


- find all connected tables
- find all links between tables
- order the table-boxes, the most left table will get the highest number
- search all top tables, i.e.tables that have no left join to another table
- put all top tables in the "FROM" clause
- walk to the tree of tables and put them in a "JOIN" clause
- check if there are joins between top tables, if found, connect them 
through the "WHERE" clause

- add the user "WHERE" to the "WHERE" clause

You can see an example here
 
http://oase.uci.kun.nl/~mientki/data_www/pic/jalcc/help/jalcc_sqlite.html#visual_link_design
(btw this page, although messy, gives an idea of the db-manager I'm 
working on)


thanks again for all your support,

--
cheers,
Stef Mientki
http://pic.flappie.nl


-
To unsubscribe, send email to [EMAIL PROTECTED]
-