Selecting Fields from a joined database

Results 1 to 2 of 2

Thread: Selecting Fields from a joined database

  1. #1
    Join Date
    Dec 1969

    Default Selecting Fields from a joined database

    I have several tables in a join as follows. This is the only way I could get them to work. (I also need to add several other tables with varying WHERE Clauses):<BR><BR>&#060;%<BR>Set MyConn = Server.CreateObject("ADODB.Connection")<BR>MyConn. Open "DSN=Data1"<BR>%&#062;<BR><BR>SELECT * FROM CompanyTable, Company_VS_CategoryTable, ContactTable WHERE Company_VS_CategoryTable.CategoryPKey="& catnum &" AND CompanyTable.PKey=Company_VS_CategoryTable.Company PKey AND CompanyTable.PKey=ContactTable.CompanyPKey%&#062;< BR><BR>&#060;%Set RS = MyConn.Execute(SQL_query)%&#062;<BR><BR>The JOIN above works except that there are duplicate field names in the tables, and whereas before I joined the tables, &#060;%fe_mail=RS("E_Mail")%&#062; would produce the correct data from the CompanyTable, it now produced a blank field, most probably from the ContactTable. However, changing the statement to read: <BR>&#060;%fe_mail=RS("CompanyTable.E_Mail")%&#062 ; <BR>produces the error: <BR><BR>ADODB.Fields error &#039 800a0cc1&#039 <BR><BR>ADO could not find the object in the collection corresponding to the name or ordinal reference requested by the application. <BR><BR>/stusasp1.asp, line 53 <BR><BR><BR>In other words, the RS statements do not recognize the fully qualified names! How can I accomplish this?<BR><BR>Thanks!<BR><BR>Stu Adler<BR><BR><BR> <BR>

  2. #2
    Garth Guest

    Default RE: Selecting Fields from a joined database

    Replace the * in the SELECT with the fully qualified field names and then use an alias on each field that exists in multiple tables. Something like...<BR><BR>SELECT table1.Field1 AS &#039Alias1&#039, table2.Field1 AS &#039Alias2&#039<BR>FROM table1, table2<BR>WHERE table1.PrimaryKey = table2.ForiegnKey<BR><BR>You can then reference the alias in your .asp. I do this a lot when I return calculated values in a SELECT.<BR><BR>Garth<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