Classic ASP Discussion - selecting 2 table at same time /please he

selecting 2 table at same time /please he

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Classic ASP Discussion
Forum Description: Discussion on Active Server Pages (Classic ASP).
URL: https://forums.webwiz.net/forum_posts.asp?TID=12297
Printed Date: 31 March 2026 at 4:48am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: selecting 2 table at same time /please he

Posted By: FARSHAD
Subject: selecting 2 table at same time /please he
Date Posted: 23 October 2004 at 1:00pm

Hi
I want select 2 table at same time
this is my code and my first table

Code:


  strSQL = "SELECT TOP "&Cint(intTopCount)& " * "
  strSQL = strSQL & "FROM T_WEBLOG "
  strSQL = strSQL & "WHERE b_published = true "
  strSQL = strSQL & "ORDER BY b_date DESC, b_time DESC;"

the above code work very good
but I want select second table

I added this code to above code

Code:


  strSQL = strSQL & "SELECT T_IMAGES.* "
  strSQL = strSQL & "FROM T_IMAGES "

so this will be my code

Code:


 strSQL = "SELECT TOP "&Cint(intTopCount)&" * "
 strSQL = strSQL & "FROM T_WEBLOG "
 strSQL = strSQL & "WHERE b_published = true "
 strSQL = strSQL & "ORDER BY b_date DESC, b_time DESC;"
 strSQL = strSQL & "SELECT T_IMAGES.* "
 strSQL = strSQL & "FROM T_IMAGES "

but after I added second code to it
I get this error
============
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Characters found after end of SQL statement.

/functions.asp, line 71

===========

whats the problem
Please help me I need it

Thanks

Replies:

Posted By: dpyers
Date Posted: 23 October 2004 at 3:32pm

the ; at the end of DESC; terminates the sql statement but your query is bad. You wouldn't use two select statements in the same query.

You need to join the two tables - http://sqlcourse2.com/joins.html - http://sqlcourse2.com/joins.html

-------------

Lead me not into temptation... I know the short cut, follow me.

Posted By: FARSHAD
Date Posted: 23 October 2004 at 6:27pm

Thanks I saw your link
But I want use " SELECT TOP 20 "
and How can I use this when I join tables?

Thanks

Posted By: FARSHAD
Date Posted: 24 October 2004 at 1:59am

and tell me so what is this :
this is a part of ACTIVE TOPICS.ASP file in WEBWIZ FORUM

this used what I want How it used this

===================


 strSQL = "SELECT " & strDbTable & "Forum.Forum_name, " & strDbTable & "Forum.Password, " & strDbTable & "Forum.Forum_code, " & strDbTable & "Topic.* "
 strSQL = strSQL & "FROM " & strDbTable & "Category, " & strDbTable & "Forum, " & strDbTable & "Topic "
 strSQL = strSQL & "WHERE ((" & strDbTable & "Category.Cat_ID = " & strDbTable & "Forum.Cat_ID AND " & strDbTable & "Forum.Forum_ID = " & strDbTable & "Topic.Forum_ID) AND (" & strDbTable & "Topic.Last_entry_date > " & strDatabaseDateFunction & " - " & dblActiveFrom & "))"
 strSQL = strSQL & " AND (" & strDbTable & "Forum.[Read] <= " & intForumGroupPermission & " OR (" & strDbTable & "Topic.Forum_ID IN ("
 strSQL = strSQL & " SELECT " & strDbTable & "Permissions.Forum_ID "
 strSQL = strSQL & " FROM " & strDbTable & "Permissions "
 strSQL = strSQL & " WHERE " & strDbTable & "Permissions.Author_ID=" & lngLoggedInUserID & " OR " & strDbTable & "Permissions.Group_ID = " & intGroupID & " AND " & strDbTable & "Permissions.[Read]=TRUE))"
 strSQL = strSQL & " )" 
 strSQL = strSQL & "ORDER BY " & strDbTable & "Category.Cat_order ASC, " & strDbTable & "Forum.Forum_Order ASC, " & strDbTable & "Topic.Last_entry_date DESC;"

it used two table in same query
could you tell me how they did that?

Thanks

Posted By: Mart
Date Posted: 24 October 2004 at 4:47am

Take a look at this http://www.w3schools.com/sql/sql_join.asp - http://www.w3schools.com/sql/sql_join.asp

Posted By: FARSHAD
Date Posted: 24 October 2004 at 6:34am

Thanks But I am using ACCESS DB
anyway I want Use two table in one query
with this code it work fine

strSQL = "SELECT T_WEBLOG.*, T_IMAGES.* "
strSQL = strSQL & "FROM T_WEBLOG, T_IMAGES "
strSQL = strSQL & "WHERE b_published = true "
strSQL = strSQL & "ORDER BY b_date DESC, b_time DESC;"

but I want select top 5 records in one of tables for example
T_WEBLOG how can I add SELECT TOP 5 to above code?

I have to use one variable

Set Rs = Server.CreateObject("ADODB.Recordset")

because I want to use LOOP function and I have to use one variable

==

?????

Posted By: ljamal
Date Posted: 24 October 2004 at 7:17am

Replace select with select top 5

-------------
L. Jamal Walton

http://www.ljamal.com/" rel="nofollow - L. Jamal Inc : Web/ Print Design and ASP Programming

Posted By: FARSHAD
Date Posted: 24 October 2004 at 11:18am

Thanks but I knew that
When we replace it with SELECT TOP 5 it will select top 5 records in each 2 tables
But I want it Just select From First Table

Posted By: dpyers
Date Posted: 24 October 2004 at 1:43pm

Look at marts link again. The SQL is for any db, Access included.

What I think you want to do is a Left Join - where all rows from the first table are listed along with any rows from the second table that match on a specific key.

The two basic problems with your sql is that it's not identifying which table the fields are coming from and we don't know what key field the two tables have in common that can be used to link them.

Each field you specify in the sql needs to be in the format of tablename.fieldname - e.g. T_WEBLOG.b_published
You need to join the two tables on some key field that is in both tables - e.g. Log_ID, WebLog_Count, ... whatever.

-------------

Lead me not into temptation... I know the short cut, follow me.

Posted By: FARSHAD
Date Posted: 24 October 2004 at 2:32pm

I know
Please read my Question

I told that this code is working fine

strSQL = "SELECT T_WEBLOG.*, T_IMAGES.* "
strSQL = strSQL & "FROM T_WEBLOG, T_IMAGES "
strSQL = strSQL & "WHERE b_published = true "
strSQL = strSQL & "ORDER BY b_date DESC, b_time DESC;"

the only problem in that I want select top 5 records from table
T_WEBLOG

Just this.
I know its possible 100 percent there is a way for this.
If you think it can be done with LEFT JOIN please write it here for me
because I don`t know how can I use LEFT JOIN with ASP
every time I get error

Thanks

Posted By: Mart
Date Posted: 24 October 2004 at 4:15pm

Use SELECT TOP 5 instead of SELECT, theres no need for left joins

Posted By: FARSHAD
Date Posted: 24 October 2004 at 6:01pm

Mart I did it but
it will select top 5 records from those 2 Tables
But I want it Just select 5 top records from one of that tables

Thanks