syntax problem

[Mikael Hellström]

hi!!
This is my problem. Im planing this without success

*i have 3 tables table1 table2 and table3...
*The tables have some fields with the same name for example
both table 2 and table 3 have the field "age"
*The field id is a key

select * from a table1, b table2, c table3 where a.id=session("memberId")
and b.id= session("memberId") and c.id=session("memberId")

MY QUESTION ARE
How do i get data from tables that have the same field name like the example
table 2 and table 3???
My guess are something like this:

response.write recordsset(b.age) and for the other
response.write recordset(c.age)..

This is wrong syntax i know, but how do i write the syntax in asp?

[Bob Barrows]

Replies inline:
Mikael Hellström wrote:

>
> *i have 3 tables table1 table2 and table3...
> *The tables have some fields with the same name for example
> both table 2 and table 3 have the field "age"
> *The field id is a key
>
> select * from a table1, b table2, c table3 where

Why do your table aliases come before the table names here? This looks like
an invalid query. What is your database?

> a.id=session("memberId") and b.id= session("memberId") and
> c.id=session("memberId")
>

Unless you are deliberately doing a cross-join (cartesian product), your
example query here does not seem to be correct. You should be relating the
tables to each other. Depending on what your database is, you should be
using the ANSI standard syntax (assuming id is a numeric column):

" ... from table1 a INNER JOIN table2 b
ON a.id=b.id INNER JOIN table3 c ON a.id=c.id
WHERE a.id=" & session("memberId")

> MY QUESTION ARE
> How do i get data from tables that have the same field name like the
> example table 2 and table 3???
> My guess are something like this:
>
> response.write recordsset(b.age) and for the other
> response.write recordset(c.age)..
>
> This is wrong syntax i know, but how do i write the syntax in asp?

This is yet another reason to avoid using "select *" (SelStar) in production
code. You should always explicitly list the columns you want your query to
return. In this case, it will allow you to use column aliases to
differentiate the like-named columns from each table. Like this (use your
own nomenclature - "AgeFrom_" is merely for the sake of the example):

Select a.id, a.col1,b.age As AgeFromB, c.age As AgeFromC ...

Then, after you open the recordset, you can use the column alias to specify
the specific age you wish to read:
response.write recordset("AgeFromB")

Avoiding SelStar will also help you avoid retrieving unnecessary data. For
example, why retrieve the id column from tables 2 and 3? You know they are
going to contain the same data as the id column in table 1: why retrieve
unnecessary data and cause extra network traffic and unnecessary use of
resources?


HTH,
Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Lee Mundie]

Hi,
Im trying to get his SQl to work but run thru a query analiser tells me
there is a syntax error and in the browser returns no results - any ideas?

SELECT m.Username, m.Author_ID, au.Author_ID AS ActiveUserAuthorID
FROM tblBuddyList bl INNER JOIN Members m ON bl.Buddy_ID = m.Author_ID
LEFT JOIN tblActiveUser au ON bl.Buddy_ID = au.Author_ID WHERE bl.Author_ID
= @LoggedInUserID AND bl.Buddy_ID <> 2
ORDER BY m.Username ASC;

It worked before modification from:

SELECT tblBuddyList.*, Members.Username, Members.Author_ID
FROM Members INNER JOIN tblBuddyList ON Members.Author_ID
= tblBuddyList.Buddy_ID WHERE tblBuddyList.Author_ID=" & lngLoggedInUserID &
"
AND tblBuddyList.Buddy_ID <> 2
ORDER BY Members.Username ASC;

Thanks in advance

Lee

[Aaron Bertrand [MVP]]

What is the EXACT error message (there are different kinds of syntax errors;
VBScript or from the database)? What is the EXACT code you are using? What
database and version are you using? Are you connecting via ODBC or OLEDB?

<followups set to asp.db only>

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]




"Lee Mundie" <lee.j.mundie@ntlworld.com> wrote in message
news:0GJvb.1965$B03.1220806@newsfep2-win.server.ntli.net...

> Hi,
> Im trying to get his SQl to work but run thru a query analiser tells

me

> there is a syntax error and in the browser returns no results - any ideas?
>
> SELECT m.Username, m.Author_ID, au.Author_ID AS ActiveUserAuthorID
> FROM tblBuddyList bl INNER JOIN Members m ON bl.Buddy_ID = m.Author_ID
> LEFT JOIN tblActiveUser au ON bl.Buddy_ID = au.Author_ID WHERE

bl.Author_ID

> = @LoggedInUserID AND bl.Buddy_ID <> 2
> ORDER BY m.Username ASC;
>
> It worked before modification from:
>
> SELECT tblBuddyList.*, Members.Username, Members.Author_ID
> FROM Members INNER JOIN tblBuddyList ON Members.Author_ID
> = tblBuddyList.Buddy_ID WHERE tblBuddyList.Author_ID=" & lngLoggedInUserID

&

> "
> AND tblBuddyList.Buddy_ID <> 2
> ORDER BY Members.Username ASC;
>
> Thanks in advance
>
> Lee
>
>

[Bob Barrows]

I was going to ask if this was Access or SQL Server, but "@LoggedInUserID"
leads me to lean more towards SQL Server. Please don't make us guess. :-)
The version of SQL Server may also be relevant.

Lee Mundie wrote:

> Hi,
> Im trying to get his SQl to work but run thru a query analiser
> tells me there is a syntax error and in the browser returns no
> results - any ideas?
>
> SELECT

m.Username,
m.Author_ID,
au.Author_ID AS ActiveUserAuthorID

> FROM tblBuddyList bl

INNER JOIN Members m ON bl.Buddy_ID = m.Author_ID

> LEFT JOIN tblActiveUser au ON bl.Buddy_ID = au.Author_ID

WHERE

> bl.Author_ID = @LoggedInUserID

AND bl.Buddy_ID <> 2

> ORDER BY m.Username ASC;

Hmm, so I'm assuming this is from a stored procedure? When you try to run it
in Query Analyzer, do you declare the @LoggedInUserID variable and give it a
value?

>
> It worked before modification from:
>
> SELECT tblBuddyList.*, Members.Username, Members.Author_ID
> FROM Members INNER JOIN tblBuddyList ON Members.Author_ID
> = tblBuddyList.Buddy_ID WHERE tblBuddyList.Author_ID=" &
> lngLoggedInUserID & "
> AND tblBuddyList.Buddy_ID <> 2
> ORDER BY Members.Username ASC;

So I'm correctly assuming you're rewriting this to get away from dynamic
sql? Good!

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Lee Mundie]

Hi,
It appears to be a VBScript Error syntax, at the INNER JOIN - using ASP,
Access DB via ODBC on JET4. The final intention is to SQL the DB, but find
it easier in initial stages to dev on Access.

Still kinda getting there with ASP, so forgive if vague or stoopid
somethimes!!!

This is the exact code:

SELECT m.Username, m.Author_ID, au.Author_ID AS ActiveUserAuthorID
FROM tblBuddyList bl INNER JOIN Members m ON bl.Buddy_ID = m.Author_ID
LEFT JOIN tblActiveUser au ON bl.Buddy_ID = au.Author_ID WHERE bl.Author_ID
= @LoggedInUserI AND bl.Buddy_ID <> 2
ORDER BY m.Username ASC;

Thanks in Advance

Lee

[Bob Barrows]

Lee Mundie wrote:

> Hi,
> It appears to be a VBScript Error syntax, at the INNER JOIN - using
> ASP, Access DB via ODBC on JET4. The final intention is to SQL the
> DB, but find it easier in initial stages to dev on Access.

Very good idea. Even better if you save the query in Access and run the
saved query from ASP. I've posted many examples of vbscript code to run
saved parameter queries in Access - a quick Google seach should easily turn
them up.

>
> Still kinda getting there with ASP, so forgive if vague or stoopid
> somethimes!!!
>
> This is the exact code:
>
> SELECT m.Username, m.Author_ID, au.Author_ID AS ActiveUserAuthorID
> FROM tblBuddyList bl INNER JOIN Members m ON bl.Buddy_ID = m.Author_ID
> LEFT JOIN tblActiveUser au ON bl.Buddy_ID = au.Author_ID WHERE
> bl.Author_ID = @LoggedInUserI AND bl.Buddy_ID <> 2
> ORDER BY m.Username ASC;
>
> Thanks in Advance
>
> Lee

Excuse me, but how can Author_ID, presumably a Number field, ever be equal
to @LoggedInUserI, a non-delimited string?

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Bob Barrows]

Lee Mundie wrote:

> Hi,
> It appears to be a VBScript Error syntax, at the INNER JOIN - using
> ASP, Access DB via ODBC on JET4. The final intention is to SQL the
> DB, but find it easier in initial stages to dev on Access.
>
> Still kinda getting there with ASP, so forgive if vague or stoopid
> somethimes!!!
>
> This is the exact code:
>
> SELECT m.Username, m.Author_ID, au.Author_ID AS ActiveUserAuthorID
> FROM tblBuddyList bl INNER JOIN Members m ON bl.Buddy_ID = m.Author_ID
> LEFT JOIN tblActiveUser au ON bl.Buddy_ID = au.Author_ID WHERE
> bl.Author_ID = @LoggedInUserI AND bl.Buddy_ID <> 2
> ORDER BY m.Username ASC;
>

OK, you're trying to use @LoggedInUserI as a parameter right? You need to
surround it with brackets []

Also, Access is very "fussy" about joining more than two tables: it wants
you to group the joins using parentheses. I suggest using GUI Design View to
create your basic query, putting all the tables in and creating all the
joins. Then switch to SQL View to see where Access puts the grouping
parentheses in the FROM clause.

HTH,
Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Aaron Bertrand [MVP]]

> It appears to be a VBScript Error syntax,

"Appears to be"? Could you COPY & PASTE the error message from the browser?

> This is the exact code:
>
> SELECT m.Username, m.Author_ID, au.Author_ID AS ActiveUserAuthorID
> FROM tblBuddyList bl INNER JOIN Members m ON bl.Buddy_ID = m.Author_ID
> LEFT JOIN tblActiveUser au ON bl.Buddy_ID = au.Author_ID WHERE

bl.Author_ID

> = @LoggedInUserI AND bl.Buddy_ID <> 2
> ORDER BY m.Username ASC;

No, that is not ASP code, that is plain text. What I mean by code is the
ASP code that generates the error.

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

Lee,

what value do u set to @LoggedInUserID?

"Lee Mundie" <lee.j.mundie@ntlworld.com> wrote in message
news:0GJvb.1965$B03.1220806@newsfep2-win.server.ntli.net...

> Hi,
> Im trying to get his SQl to work but run thru a query analiser tells

me

> there is a syntax error and in the browser returns no results - any ideas?
>
> SELECT m.Username, m.Author_ID, au.Author_ID AS ActiveUserAuthorID
> FROM tblBuddyList bl INNER JOIN Members m ON bl.Buddy_ID = m.Author_ID
> LEFT JOIN tblActiveUser au ON bl.Buddy_ID = au.Author_ID WHERE

bl.Author_ID

> = @LoggedInUserID AND bl.Buddy_ID <> 2
> ORDER BY m.Username ASC;
>
> It worked before modification from:
>
> SELECT tblBuddyList.*, Members.Username, Members.Author_ID
> FROM Members INNER JOIN tblBuddyList ON Members.Author_ID
> = tblBuddyList.Buddy_ID WHERE tblBuddyList.Author_ID=" & lngLoggedInUserID

&

> "
> AND tblBuddyList.Buddy_ID <> 2
> ORDER BY Members.Username ASC;
>
> Thanks in advance
>
> Lee
>
>

[John Olav]

Okay, I'm a newbie, the following script are commented and explained, but
the something is wrong with the syntax. Anyone, any idea?

<php for login check and redirect of users according to their login details
against data in MySQL>

// connection - custnumber and orgnmmer associated with similar login fields
in the login form, values must be equal to DB-fields
mysql_connect("my.ip.address", "$custnumber", "@orgnummer") or
die(mysql_error());
mysql_select_db("custdatabase") or die(mysql_error());

// select relevant table info
$query "SELECT custnumber,orgnummer,rabkat FROM kunder";

//check to see if login details are equal to those in db and whether
"rabkat" are value 10 or 20, redirect users logged in according to the value
in DB under column "rabkat"

if (custnumber=="$custnumber")
{
orgunmmer=="$orgnummer";
rabkat==10;
echo "<META HTTP-EQUIV=\"Refresh\" CONTENT=\"5; URL=html-redirect1.html\">";
}

if (custnumber=="$custnumber")
{
orgnummer=="$orgnummer";
rabkat==20;
echo "<META HTTP-EQUIV=\"Refresh\" CONTENT=\"5; URL=html-redirect2.html\">";
}
else if (custnumber=="$custnumber")
{
orgnummer=="$orgnummer";
rabkat="";
echo "<META HTTP-EQUIV=\"Refresh\" CONTENT=\"5; URL=html-redirect3.html\">";
}

[Brian Wakem]

John Olav wrote:

> Okay, I'm a newbie, the following script are commented and explained, but
> the something is wrong with the syntax. Anyone, any idea?
>
> <php for login check and redirect of users according to their login
> details against data in MySQL>
>
> // connection - custnumber and orgnmmer associated with similar login
> fields in the login form, values must be equal to DB-fields
> mysql_connect("my.ip.address", "$custnumber", "@orgnummer") or
> die(mysql_error());
> mysql_select_db("custdatabase") or die(mysql_error());
>
> // select relevant table info
> $query "SELECT custnumber,orgnummer,rabkat FROM kunder";
>
> //check to see if login details are equal to those in db and whether
> "rabkat" are value 10 or 20, redirect users logged in according to the
> value in DB under column "rabkat"
>
> if (custnumber=="$custnumber")
> {
> orgunmmer=="$orgnummer";
> rabkat==10;
> echo "<META HTTP-EQUIV=\"Refresh\" CONTENT=\"5;
> URL=html-redirect1.html\">"; }
>
> if (custnumber=="$custnumber")
> {
> orgnummer=="$orgnummer";
> rabkat==20;
> echo "<META HTTP-EQUIV=\"Refresh\" CONTENT=\"5;
> URL=html-redirect2.html\">"; }
> else if (custnumber=="$custnumber")
> {
> orgnummer=="$orgnummer";
> rabkat="";
> echo "<META HTTP-EQUIV=\"Refresh\" CONTENT=\"5;
> URL=html-redirect3.html\">"; }

I can't commment on the php, having never touched it, but was is obvious to
me is that you have 3 different spellings of org...
(orgnmmer,orgnummer,orgunmmer). Surely at least 2 of them are wrong.


--
Brian Wakem
Email: [url]http://homepage.ntlworld.com/b.wakem/myemail.png[/url]

[Jerry Stuckle]

Hi, John,

Comments interspaced below...

John Olav wrote:

> Okay, I'm a newbie, the following script are commented and explained, but
> the something is wrong with the syntax. Anyone, any idea?
>
> <php for login check and redirect of users according to their login details
> against data in MySQL>
>
> // connection - custnumber and orgnmmer associated with similar login fields
> in the login form, values must be equal to DB-fields
> mysql_connect("my.ip.address", "$custnumber", "@orgnummer") or
> die(mysql_error());
> mysql_select_db("custdatabase") or die(mysql_error());
>
> // select relevant table info
> $query "SELECT custnumber,orgnummer,rabkat FROM kunder";
>

This is invalid syntax. You need to call mysql_query to actually do the query, i.e.

$query = "SELECT custnumber,orgnummer,rabkat FROM kunder";
$result= mysql_query($query);

> //check to see if login details are equal to those in db and whether
> "rabkat" are value 10 or 20, redirect users logged in according to the value
> in DB under column "rabkat"
>

You've done the query, but now you must fetch the result:

$data = mysql_fetch_array($result);

Results of the first matching row will be in $data['custnumber'],
$data['orgnummer'], etc.

> if (custnumber=="$custnumber")

if ($data['custnumber'] == $custnumber)

I'm assuming $custnumber is a variable here - so no quotes around it

> {
> orgunmmer=="$orgnummer";

$orgnummer = $data['orgnummer'];

> rabkat==10;
> echo "<META HTTP-EQUIV=\"Refresh\" CONTENT=\"5; URL=html-redirect1.html\">";
> }
>
> if (custnumber=="$custnumber")

Not sure why you're checking this again - it's the same data as before

> {
> orgnummer=="$orgnummer";
> rabkat==20;
> echo "<META HTTP-EQUIV=\"Refresh\" CONTENT=\"5; URL=html-redirect2.html\">";
> }
> else if (custnumber=="$custnumber")
> {
> orgnummer=="$orgnummer";
> rabkat="";
> echo "<META HTTP-EQUIV=\"Refresh\" CONTENT=\"5; URL=html-redirect3.html\">";
> }
>
>

If you're looking for a specific row, you should use the WHERE clause to fetch
that row, i.e.

$query = "SELECT orgnummer,rabkat FROM kunder WHERE custnumber=$custnumber";

Assuming that $custnumber is a numeric value.

Hopefully this will get you started. I'm not sure what you're trying to do, so
I can't comment further on your code structure.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
[email]jstucklex@attglobal.net[/email]
==================