Very strange SQL problem

Results 1 to 2 of 2

Thread: Very strange SQL problem

  1. #1
    Join Date
    Dec 1969

    Default Very strange SQL problem

    SELECT * FROM (Establishments INNER JOIN EstablishmentType ON Establishments.iType = EstablishmentType.ID) WHERE Establishments.iLocation = " & sSPECIFIC & " AND EstablishmentType.strName LIKE &#039;Restaurant%&#039; GROUP BY Establishments.iType ORDER BY Establishments.strEstablishmentName;<BR><BR>For one, it says I can&#039;t GROUP BY fields selected with a *. Why?<BR><BR>Secondly, and more importantly, when I open this SQL statement with myrecordset.Open sql, connection (... in VBScript) and I try to use the ID i.e. myrecordset("ID"), it does NOT grab the ID from the Establishments table.<BR><BR>It grabs instead the ID from EstablishmentType! Yet, for all the other columns in Establishments, if I use myrecordset("columname"), it grabs the appropriate column.<BR><BR>What on earth is going on?

  2. #2
    Join Date
    Dec 1969

    Default RE: Very strange SQL problem

    You should newer use select * but rather specify the columns that you need explicitly. If there are multiple columns with the same name you need to use an alias to avoid any ambiguity, e.g.<BR><BR>select , as t2id from t1 natural join t2<BR><BR>and then use <BR><BR>rs("t2id")<BR><BR>to reference the id column in t2.<BR><BR>Try to get hold on some sql documentation and read about group by.

Posting Permissions

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