Results 1 to 2 of 2

Thread: SQL 2 SELECTS & a WHERE??

  1. #1
    Join Date
    Dec 1969

    Default SQL 2 SELECTS & a WHERE??

    Hi Guys,<BR><BR>I have two tables in a database. Once called Auth and one called Users.<BR><BR>I need to create and SQL query that reads...<BR><BR>"SELECT Owner From Auth AND SELECT User FROM Users WHERE Access_level &#062; 1"<BR><BR>I have written it this way (from the ACCESS SHOW SQL QUERY) but It doesnt seem to work correctly. Is the syntax correct??<BR><BR>SQLstmt = "SELECT Auth.Owner, Users.User FROM Auth, Users WHERE auth_level &#062; 1 "<BR><BR>&#039 then test the output<BR><BR>Set rs = conn.Execute(SQLstmt)<BR>%&#062;<BR>&#060;%IF NOT RS.EOF THEN <BR> DO WHILE NOT rs.EOF%&#062;<BR>&#060;%=rs("User")%&#062;<BR><BR> &#060;%<BR> rs.MoveNext<BR> loop<BR> rs.Close<BR> Response.Write "the end"<BR>END if<BR>%&#062;<BR><BR>It ends up printing the correct users - but prints them 7 times each. What have I done wrong? :)<BR><BR>Any help would be appreciated :))0<BR><BR>Renee :))

  2. #2
    Richard A. Lowe Guest

    Default RE: SQL 2 SELECTS & a WHERE??

    The SQL statement you presented works, it just doesn&#039t consider the relationship between the two tables you specify. <BR><BR>For example, if Users has 5 records and Auth has 10 then the statement:<BR><BR>SELECT Auth.Owner, Users.User FROM Auth, Users <BR><BR>Will return 50 records (called a Cartesian Product). Presumably, there is some relationship, some field that links these tables togther. In order to return the limited set of records you want, you have to perform a JOIN on these table, using that field. I&#039ll take a guess that you have a UserID field:<BR><BR>SELECT a.Owner, u.User <BR>FROM Auth a<BR>INNER JOIN Users u<BR>ON a.UserID = u.UserID<BR>WHERE a.auth_level &#062; 1 <BR><BR>This should return the result set you want (or closer, anyway).<BR><BR>Richard<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