Nested queries

Results 1 to 2 of 2

Thread: Nested queries

  1. #1
    Join Date
    Dec 1969
    Posts
    241

    Default Nested queries

    &#039 I have build an sql like this:<BR><BR>qu="SELECT Intestatario FROM TbWeb_Sicurezza WHERE Utente_Web = " & "&#039" & u & "&#039 "<BR>qu=qu & "AND EXISTS (SELECT Codice, Denominazione FROM TbAnagra WHERE [Codice] = [TbWeb_Sicurezza].[Intestatario]) "<BR>set ru=conn.Execute(qu)<BR><BR>&#039 I can easily get data from the first query<BR><BR>response.write ru.fields("Intestatario")<BR><BR>&#039 but how can I get data from the nested one, since if I try<BR><BR>response.write ru.fields("Denominazione")<BR><BR>&#039 I get this error<BR>ADODB.Fields error &#039 800a0cc1&#039 <BR>ADO could not find the object in the collection corresponding to the name or ordinal reference requested by the application. <BR>/zse.asp, line 43 <BR><BR>&#039 ???<BR>&#039 Thank you<BR>&#039 Simon<BR>

  2. #2
    Join Date
    Dec 1969
    Posts
    511

    Default RE: Nested queries

    This is how your SQL statement reads at the minute, always a good idea to include a nicely formatted version in your questions, makes it easier to read.<BR><BR>SELECT Intestatario <BR>FROM TbWeb_Sicurezza<BR>WHERE Utente_Web = &#039TEST&#039 AND EXISTS (<BR> SELECT Codice, Denominazione<BR> FROM TbAnagra <BR> WHERE Codice = TbWeb_Sicurezza.Intestatario) <BR><BR>You problem lies in your first Select statement, this is the one that returns results and you have only asked it to return the "Intestatario" field. The second Select statement only checks to see if the values exist, nested Select statements do not return value to the main select statement for display. All you have to do to fix it is add the "Denominazione" field to the main select statement and include the "TbAnagra" table in the From clause like so :<BR><BR>SELECT Intestatario, Denominazione <BR>FROM TbWeb_Sicurezza, TbAnagra<BR>WHERE Utente_Web = &#039TEST&#039 AND EXISTS (<BR> SELECT Codice, Denominazione<BR> FROM TbAnagra <BR> WHERE Codice = TbWeb_Sicurezza.Intestatario) <BR> <BR>hope that helps.<BR><BR>Pete<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •