[sqlite] are nested joins possible ?
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 ?
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 ?
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 ?
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 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] -