[SQL] Combine 'left outer join' and 'inner join'
Hello, I have a problem with a sql request on Postgresql. It uses 'left outer join' and 'inner join' Here is my request: select a.acte_id,a.acte_libelle,a.acte_url_image,a.acte_page, a.acte_texte,a.acte_marge_texte,a.acte_date,a.acte_registre_page, commune.commune_id,commune.commune_nom,commune.commune_nom_equivalent, departement.departement_id,departement.departement_nom,departement.departement_numero, pays.pays_id,pays.pays_nom, t.type_acte_id,t.type_acte_nom, r.registre_id,r.registre_cote,r.registre_libelle,r.registre_annee_debut, r.registre_annee_fin,r.registre_disponible,r.registre_repertoire,r.registre_transformation, source.source_id,source.source_libelle,source.source_description, source.source_cote,source.source_auteur, archive.archive_id,archive.archive_libelle,archive.archive_libelle_recherche, adresse_id,adresse_libelle,adresse_libelle1,adresse_libelle2, commune1.commune_id as commune1_id, commune1.commune_nom as commune1_nom, commune1.commune_nom_equivalent as commune1_nom_equivalent, departement1.departement_id as departement1_id, departement1.departement_nom as departement1_nom, departement1.departement_numero as departement1_numero, pays1.pays_id as pays1_id,pays1.pays_nom as pays1_nom from acte a left outer join registre as r on ( a.acte_registre_id = r.registre_id ) inner join commune on ( a.acte_lieu_id = commune.commune_id ) inner join departement on ( commune.commune_departement_id = departement.departement_id ) inner join pays on ( departement.departement_pays_id = pays.pays_id ) inner join type_acte as t on ( a.acte_type_id=t.type_acte_id ) inner join source on ( a.acte_source_id=source.source_id ) left outer join archive on ( source.source_archive_id = archive.archive_id ) inner join adresse on ( archive.archive_adresse_id = adresse.adresse_id ) left outer join commune as commune1 on ( adresse.adresse_commune_id = commune1.commune_id ) left outer join departement as departement1 on ( commune1.commune_departement_id = departement1.departement_id ) left outer join pays as pays1 on ( departement1.departement_pays_id = pays1.pays_id ) and a.acte_id=1; It is based on the data model that you can find at the following url: http://cvs.sourceforge.net/cgi-bin/viewcvs.cgi/jgenea/jgenea-dao/src/sql/hypersonic/crebas-hsql.sql?rev=1.18&content-type=text/vnd.viewcvs-markup This request must return only one line but the 'left outer join', there are several lines. I think that I don't correctly use 'left outer join'... Is anyone know how to use it? Thanks by advance for your help. Thierry xxx Ce message et toutes les pieces jointes (ci - apres le \"message\" ) sont etablis a l'attention exclusive de ses destinataires et sont strictement confidentiels. Si vous n'etes pas le destinataire du message, il vous est interdit d'en faire la copie, de le faire suivre, d'en divulguer le contenu ou de l'utiliser en tout ou partie. Si vous avez recu ce message par erreur, merci d'en avertir immediatement l'expediteur et de le detruire. L'integrite du message n'est pas assuree sur Internet, chaque information pouvant etre interceptee, modifiee, perdue, subir un retard dans sa transmission ou contenir des virus. L'expediteur decline donc toute responsabilite pour toute alteration, deformation ou falsification subie par le message au cours de sa transmission. Toute opinion contenue dans ce message appartient a son auteur et ne peut engager la responsabilite du CCF ou de l'entite expeditrice du message, a moins que cela ait ete clairement specifie dans le message et qu'il soit verifie que son auteur etait en mesure d'engager le CCF ou ladite entite. xxx This message and any attachments are confidential to the ordinary user of the e-mail address to which it was addressed and may also be privileged. If you are not the addressee you may not copy, forward, disclose or use any part of the message or its attachments and if you have received this message in error, please notify the sender immediately by return e-mail and delete it from your system. Internet communications cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, arrive late or contain viruses. The sender therefore does not accept liability for any errors or omissions in the context of this message which arise as a result of Internet transmission. Any opinions contained in this message are those of the author and are not given or endorsed by the CCF or office through which this message is sent unless otherwise clearly indicated in this message and the authority of the author to so bind the CCF entity referred to is duly verified. xxx ---(end of broadcast)---
[SQL] Problem using Subselect results
Hi all, I want to use the result of a subselect as condition in another one. table1: a,b table2: a,c CREATE VIEW my_view AS SELECT b,c (SELECT a, b FROM table1 WHERE b=1) my_ab, (SELECT c FROM table2, my_ab WHERE table3.a=my_ab.a) my_c; this is just an example - i know i could cross join this one, but i need to refer to the results of several subselects in several other. does return "relation my_ab unknown". it is not just a problem of execution order - if i turn it the other way round it's still the same. Am I just trying to do something really stupid? And what for is the (necessary) AS statement for subselects, if it's not possible to access their results by that name? And as I need the result of a subselect in several other subselects it's not possible to transform them into a cascade of sub, subsub, subsubsub selects. Any ideas? TIA, Oliver - This mail sent through IMP: http://horde.org/imp/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Problem using Subselect results
> I want to use the result of a subselect as condition in another one. > > table1: a,b > table2: a,c > > CREATE VIEW my_view AS SELECT b,c > (SELECT a, b FROM table1 WHERE b=3D1) my_ab, > (SELECT c FROM table2, my_ab WHERE table3.a=3Dmy_ab.a) my_c; > > this is just an example - i know i could cross join this one, but i need = > to=20 > refer to the results of several subselects in several other. > > > does return "relation my_ab unknown". it is not just a problem of executi= > on=20 > order - if i turn it the other way round it's still the same. > > Am I just trying to do something really stupid? And what for is the (nece= > ssary)=20 > AS statement for subselects, if it's not possible to access their results= > by=20 > that name? > > And as I need the result of a subselect in several other subselects it's= > not=20 > possible to transform them into a cascade of sub, subsub, subsubsub s= > elects. > Any ideas?=20 > Does this match your intentions: CREATE VIEW my_view AS SELECT b,c FROM (SELECT b,c FROM table2, (SELECT a, b FROM table1 WHERE b=3D1) my_ab WHERE table3.a=3Dmy_ab.a) my_c; I assume the reference table3.a is a typo. Regards, Christoph ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Problem using Subselect results
SELECT my_c.b, my_c.c FROM (SELECT table2.b, table2.c FROM table2, (SELECT table1.a, table1.b FROM table1 WHERE (table1.b = 1)) my_ab WHERE (table2.a = my_ab.a)) my_c; You were doing what I wanted to avoid - you are using a "SUBSUBSELECT". But (now) I believe it's not possible to refer to a subselect's resultset on the same level of hierarchy - which sounds rather meaningful - because you couldn't tell which of them was being processsed first. So I'll have to get my SELECT statement into some kind of hierarchy, which makes things a bit more complicated (with twentysomething SELECT statements) Thanks, Oliver Quoting Christoph Haller <[EMAIL PROTECTED]>: > Does this match your intentions: > CREATE VIEW my_view AS SELECT b,c FROM > (SELECT b,c FROM table2, (SELECT a, b FROM table1 WHERE b=3D1) my_ab > WHERE table3.a=3Dmy_ab.a) my_c; > I assume the reference table3.a is a typo. > > Regards, Christoph > > > I want to use the result of a subselect as condition in another one. > > > > table1: a,b > > table2: a,c > > > > CREATE VIEW my_view AS SELECT b,c > > (SELECT a, b FROM table1 WHERE b=3D1) my_ab, > > (SELECT c FROM table2, my_ab WHERE table3.a=3Dmy_ab.a) my_c; > > > > this is just an example - i know i could cross join this one, but i > need = > > to=20 > > refer to the results of several subselects in several other. > > > > > > does return "relation my_ab unknown". it is not just a problem of > executi= > > on=20 > > order - if i turn it the other way round it's still the same. > > > > Am I just trying to do something really stupid? And what for is the > (nece= > > ssary)=20 > > AS statement for subselects, if it's not possible to access their > results= > > by=20 > > that name? > > > > And as I need the result of a subselect in several other subselects > it's= > > not=20 > > possible to transform them into a cascade of sub, subsub, > subsubsub s= > > elects. > > Any ideas?=20 > > - This mail sent through IMP: http://horde.org/imp/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Problem using Subselect results
[EMAIL PROTECTED] wrote: SELECT my_c.b, my_c.c FROM (SELECT table2.b, table2.c FROM table2, (SELECT table1.a, table1.b FROM table1 WHERE (table1.b = 1)) my_ab WHERE (table2.a = my_ab.a)) my_c; You were doing what I wanted to avoid - you are using a "SUBSUBSELECT". What about: CREATE VIEW my_view AS SELECT b,c from (SELECT a, b FROM table1 WHERE b=1) as my_ab, (SELECT a, c FROM table2) as my_ac WHERE my_ac.a=my_ab.a This looks like what you are trying to do, and doesn't use that 'subsubselect' you were trying to avoid... BTW, what is special to the second-level subselect, compared to the first level one? Why are you trying to avoid one, but not the other? I mean, I could understand, if you (like me) just hated subselects alltogether (then you would have converted your query into a join), but it looks like you don't... Dima But (now) I believe it's not possible to refer to a subselect's resultset on the same level of hierarchy - which sounds rather meaningful - because you couldn't tell which of them was being processsed first. So I'll have to get my SELECT statement into some kind of hierarchy, which makes things a bit more complicated (with twentysomething SELECT statements) Thanks, Oliver Quoting Christoph Haller <[EMAIL PROTECTED]>: Does this match your intentions: CREATE VIEW my_view AS SELECT b,c FROM (SELECT b,c FROM table2, (SELECT a, b FROM table1 WHERE b=3D1) my_ab WHERE table3.a=3Dmy_ab.a) my_c; I assume the reference table3.a is a typo. Regards, Christoph I want to use the result of a subselect as condition in another one. table1: a,b table2: a,c CREATE VIEW my_view AS SELECT b,c (SELECT a, b FROM table1 WHERE b=3D1) my_ab, (SELECT c FROM table2, my_ab WHERE table3.a=3Dmy_ab.a) my_c; this is just an example - i know i could cross join this one, but i need = to=20 refer to the results of several subselects in several other. does return "relation my_ab unknown". it is not just a problem of executi= on=20 order - if i turn it the other way round it's still the same. Am I just trying to do something really stupid? And what for is the (nece= ssary)=20 AS statement for subselects, if it's not possible to access their results= by=20 that name? And as I need the result of a subselect in several other subselects it's= not=20 possible to transform them into a cascade of sub, subsub, subsubsub s= elects. Any ideas?=20 - This mail sent through IMP: http://horde.org/imp/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] PostgreSQL or pl/psSQL equivalent to MS SQL Server's xp_cmdshell?
Title: PostgreSQL or pl/psSQL equivalent to MS SQL Server's xp_cmdshell? Does anyone know of the PostgreSQL or pl/psSQL equivalent to MS SQL Server's xp_cmdshell? This is the command that allows you issue command-line statements from within SQL, e.g., you would do xp_cmdshell 'dir c:\' if you wanted to see the contents of the c: drive. TM ** The information transmitted herewith is sensitive information intended only for use by the individual or entity to which it is addressed. If the reader of this message is not the intended recipient, you are hereby notified that any review, retransmission, dissemination, distribution, copying or other use of, or taking of any action in reliance upon this information is strictly prohibited. If you have received this communication in error, please contact the sender and delete the material from your computer.
Re: [SQL] PostgreSQL or pl/psSQL equivalent to MS SQL Server's
On Thu, 24 Jul 2003 [EMAIL PROTECTED] wrote: > Does anyone know of the PostgreSQL or pl/psSQL equivalent to MS SQL Server's > xp_cmdshell? This is the command that allows you issue command-line > statements from within SQL, e.g., you would do xp_cmdshell 'dir c:\' if you > wanted to see the contents of the c: drive. That can only be done inside an untrusted procedural language. plpgsql is trusted, so it can't do that type of thing. C, pltclu, plprelu, and, as of 7.4 plpython will let you do that. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] obtuse plpgsql function needs
Bruce-- Something for the todo list. This would be extremely handy. At minimum C functions should be able to ask the type of thing that was actually passed in and get a legitimate answer even if the type were a rowtype. This will also lead to the need for unnamed rowtypes, sooner or later. I know, I know, send a patch. --elein On Thu, Jul 24, 2003 at 01:07:18AM -0400, Tom Lane wrote: > elein <[EMAIL PROTECTED]> writes: > > So, other than C, plperl or pltcl is the way to go. > > As long as they can input generic composite types > > (I wasn't sure of that, but I should have known), > > Come to think of it, that is a problem: we don't have any way to declare > a function as taking "any tuple type". So even though pltcl or plperl > functions could be written to work with such input, we can't declare them. > This is a problem even for C functions. You could declare a C function > as taking "any", but then you can't even check that what you got was a > tuple ... > > Something to work on for 7.5, I suppose. > > regards, tom lane > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] min() and NaN
Greg Stark <[EMAIL PROTECTED]> writes: > Does postgres intend to support all the different types of NaN? Does you > intend to have +Inf and -Inf and underflow detection and all the other goodies > you actually need to make it useful? We have some of that; it needs work, and it's always going to be dependent on the platform having proper IEEE support, but that's no excuse to throw it away. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] min() and NaN
Tom Lane <[EMAIL PROTECTED]> writes: > NULL can be special, because it acts specially in comparisons anyway. > But NaN is just a value of the datatype. Does postgres intend to support all the different types of NaN? Does you intend to have +Inf and -Inf and underflow detection and all the other goodies you actually need to make it useful? If not it seems more useful to just use the handy unknown-value thing SQL already has and turn NaN into a NULL. -- greg ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] silly NULL question
Howdy, I'm sure most of you PostgreSQL users out there will be able to answer this, but I'm just not finding the answer. Let's say I have a table called project that has three fields: field| type --- id int name varchar(30) parent_id int right now there is only one row in the table: id | name | parent_id --- 1| silly| You'll notice there is nothing in the parent_id as there is no parent for this particular project. Previously (with PostgreSQL 7.1) a query of: select * from project where name = 'silly' AND parent_id = NULL; Would return the row. Now with an upgrade to PostgreSQL 7.3 (yes, I know there are many changes and we're working through them right now) the same query returns nothing. Dropping the "AND parent_id = NULL" returns the row as expected. Now, what is the correct specifier for an empty int value? Or, are there suggestions for how to create that column (like using NULL as the default maybe) that experts can clue-stick me with? Thanks for your time. If this is the wrong list to ask these questions to please point me in the right direction. -dan ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] silly NULL question
> Would return the row. Now with an upgrade to PostgreSQL 7.3 (yes, I know > there are many changes and we're working through them right now) the same > query returns nothing. Dropping the "AND parent_id = NULL" returns the row > as expected. NULL is similar to UNKNOWN. So, NULL = NULL is the similar to UNKNOWN = UNKNOWN. Since you don't know it, how can you tell if they're equal or not? Syntax you're looking for is: AND parent_id IS NULL If you really really really need = NULL (due to some MS product which ignores SQL standards -- say MS Access) there is a toggle in the postgresql.conf file to allow automated conversion of = NULL to IS NULL within the server. signature.asc Description: This is a digitally signed message part
Re: [SQL] silly NULL question
> "RT" == "Rod Taylor" <[EMAIL PROTECTED]>: RT> NULL is similar to UNKNOWN. RT> RT> So, NULL = NULL is the similar to UNKNOWN = UNKNOWN. Since you don't RT> know it, how can you tell if they're equal or not? RT> RT> Syntax you're looking for is: AND parent_id IS NULL ah, of course. Thanks Rod. RT> If you really really really need = NULL (due to some MS product which RT> ignores SQL standards -- say MS Access) there is a toggle in the RT> postgresql.conf file to allow automated conversion of = NULL to IS NULL RT> within the server. ah ha! the culprit. I see this was default through 7.1. Thanks again for accurate and quick reply. -dan ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Equivalent to sql%rowcount or @@rowcount
Hi All! is it possible to get in sql number of rows affected by the sql last insert, update or delete statement?? for eg, oracle - sql%rowcount. sqlserver select @@rowcount. Any help will be highly appreciated.. Thanks Regards, VijayCool new emoticons. Lots of colour! On MSN Messenger V6.0
