I am new to sql and am trying to learn it and understand how to use the queries.
I can query for basic information, no problem but I am trying something more
complex and am having trouble. I'm trying to return up to 10 records but every
time I test in my browser I only see the first record. I dont understand why I
can't see more then just that one record.
There are three tables. The names & descriptions and URL and Links are part of
the same table. They are called:
TableMain
TableNames
TableDescriptions
TableURL
TableLinks
So the names and descripts are in one table and URL and Links in another.
Here is the sql query code:
[CODE]
strSQL = "SELECT *, TableMain.ID AS Expr1, TableMain.Unit AS Expr2,
TableMain.Air_Date AS Expr3, TableNames.ID AS Expr4, Table.Names AS Expr5,
Table.Descriptions AS Expr6, TableLinks.URL AS Expr7 FROM TableMain CROSS JOIN
TableNames CROSS JOIN TableLinks WHERE (DATEDIFF(day, GETDATE(), Table.Air_Date) = 0) AND (Table.Air_Date <= GETDATE())"
[/CODE]
I was told that I dont really need to use Expr but every time I pull them out of
this code segment and re-paste into the query editor then click on Run, it puts
them back in and shows the correct result. It's working ok in the query editor so
why wont it show me more then just the first record inside of TableNames when
tested from a browser?
I was told the other day that it was because I wasn't looping through the
records. But I've tried using:
<%Do While Not RS.EOF%>
my html / asp display code in here....
<strong><% Response.Write RS ("TableName") %> </strong> <% Response.Write
RS("TableDescription") %><br /><br />
Related Links:<br />
<li class='basic'><A HREF="<%= RS("URL") %>"><%= RS("Description") %></A></li>
<%
rs.MoveNext
Loop
%>
Please any help with this would be great
First, let me respond to the issue of 'Expr#'. This is an 'Alias', an assigned name for a column that doesn't have a name. When you use [ SELECT * ], you are asking for all columns in the table, and then when you list the columns, as in [ TableMain.ID ], it produces another column with the same name. Two columns having the same name are not allowed, so an 'Alias' is assigned for one of them.
Listing the columns, and NOT using [ SELECT * ] is the preferred method.
Now about the use of CROSS JOINS. A CROSS JOIN produces every possible combination from the two tables in the JOIN. If one table has 1000 rows, and the other table has 1000 rows, a CROSS JOIN will produce 1,000,000 rows. And if you add a thrid table as a CROSS JOIN, and it has only 100 rows, the result is 100,000,000 rows. Are you sure that is your intention?
About displaying 10 records. Are you using a grid control? How are you displaying the records?
sql