|
 |
asp_databases thread: Different syntax for SQL?
Message #1 by "Joe" <bugmania76@y...> on Mon, 9 Sep 2002 07:44:14
|
|
Hi guys, i wanted to display all record using sql in regardless of whether
it is empty, In oracle sql, i could use the outerjoin (+) in the Where
clause but it seems that access sql does not support this function.
Is there a replacement for it?
Thanks,
Joe
Message #2 by "Samir Chitkara , Gurgaon" <chitkaras@g...> on Mon, 9 Sep 2002 12:45:00 +0530
|
|
replace + with *
ex:
where table1.col1 = * table2.colo2
-----Original Message-----
From: Joe [mailto:bugmania76@y...]
Sent: Monday, September 09, 2002 1:14 PM
To: ASP Databases
Subject: [asp_databases] Different syntax for SQL?
Hi guys, i wanted to display all record using sql in regardless of whether
it is empty, In oracle sql, i could use the outerjoin (+) in the Where
clause but it seems that access sql does not support this function.
Is there a replacement for it?
Thanks,
Joe
Message #3 by "Joe" <bugmania76@y...> on Mon, 9 Sep 2002 16:13:11
|
|
Humm..i've tried that but it seems that it still have syntax error, this
is wat i've written:
strSQL = "SELECT s.counter,s.survey_code, s.survey_option,
o.option_description, c.code_description FROM survey_stat s"
strSQL = strSQL & ",survey_option o,survey_code c WHERE
s.survey_code=*o.survey_code AND s.survey_option=o.survey_option "
strSQL = strSQL & " AND s.survey_code=c.survey_code AND s.year=" &
yearfield & " AND s.month=" & monthfield & " AND s.survey_code=" & question
The asterisk is in line 4..
Regards,
Joe
> replace + with *
ex:
where table1.col1 = * table2.colo2
-----Original Message-----
From: Joe [mailto:bugmania76@y...]
Sent: Monday, September 09, 2002 1:14 PM
To: ASP Databases
Subject: [asp_databases] Different syntax for SQL?
Hi guys, i wanted to display all record using sql in regardless of whether
it is empty, In oracle sql, i could use the outerjoin (+) in the Where
clause but it seems that access sql does not support this function.
Is there a replacement for it?
Thanks,
Joe
Message #4 by "Peter Foti (PeterF)" <PeterF@S...> on Mon, 9 Sep 2002 11:47:06 -0400
|
|
Use the following syntax:
SELECT {[table.]field1 [AS alias1] [, [table.]field2 [AS alias2] [, ...]]}
FROM table1 [ LEFT | RIGHT ] JOIN table2
ON table1.field1 compopr table2.field2
[WHERE... ]
It looks like you are trying to setup an alias to the table name. Can't do
that. So I would suggest modifying the SELECT portion to use the full table
names and change your FROM clause. You will also create your JOIN in the
FROM clause, not the WHERE clause.
Hope this helps.
Peter
> -----Original Message-----
> From: Joe [mailto:bugmania76@y...]
> Sent: Monday, September 09, 2002 4:13 PM
> To: ASP Databases
> Subject: [asp_databases] RE: Different syntax for SQL?
>
>
> Humm..i've tried that but it seems that it still have syntax
> error, this
> is wat i've written:
>
> strSQL = "SELECT s.counter,s.survey_code, s.survey_option,
> o.option_description, c.code_description FROM survey_stat s"
> strSQL = strSQL & ",survey_option o,survey_code c WHERE
> s.survey_code=*o.survey_code AND s.survey_option=o.survey_option "
> strSQL = strSQL & " AND s.survey_code=c.survey_code AND s.year=" &
> yearfield & " AND s.month=" & monthfield & " AND
> s.survey_code=" & question
>
>
> The asterisk is in line 4..
>
> Regards,
> Joe
>
>
> > replace + with *
>
> ex:
>
> where table1.col1 = * table2.colo2
>
> -----Original Message-----
> From: Joe [mailto:bugmania76@y...]
> Sent: Monday, September 09, 2002 1:14 PM
> To: ASP Databases
> Subject: [asp_databases] Different syntax for SQL?
>
>
> Hi guys, i wanted to display all record using sql in
> regardless of whether
> it is empty, In oracle sql, i could use the outerjoin (+) in
> the Where
> clause but it seems that access sql does not support this function.
> Is there a replacement for it?
>
> Thanks,
> Joe
>
Message #5 by "Samir Chitkara , Gurgaon" <chitkaras@g...> on Mon, 9 Sep 2002 21:21:33 +0530
|
|
hi!
use left outer join
SELECT products.prod_id, orders.price
FROM orders INNER JOIN products ON
orders.prod_id = products.prod_id
-----Original Message-----
From: Peter Foti (PeterF) [mailto:PeterF@S...]
Sent: Monday, September 09, 2002 9:17 PM
To: ASP Databases
Subject: [asp_databases] RE: Different syntax for SQL?
Use the following syntax:
SELECT {[table.]field1 [AS alias1] [, [table.]field2 [AS alias2] [, ...]]}
FROM table1 [ LEFT | RIGHT ] JOIN table2
ON table1.field1 compopr table2.field2
[WHERE... ]
It looks like you are trying to setup an alias to the table name. Can't do
that. So I would suggest modifying the SELECT portion to use the full table
names and change your FROM clause. You will also create your JOIN in the
FROM clause, not the WHERE clause.
Hope this helps.
Peter
> -----Original Message-----
> From: Joe [mailto:bugmania76@y...]
> Sent: Monday, September 09, 2002 4:13 PM
> To: ASP Databases
> Subject: [asp_databases] RE: Different syntax for SQL?
>
>
> Humm..i've tried that but it seems that it still have syntax
> error, this
> is wat i've written:
>
> strSQL = "SELECT s.counter,s.survey_code, s.survey_option,
> o.option_description, c.code_description FROM survey_stat s"
> strSQL = strSQL & ",survey_option o,survey_code c WHERE
> s.survey_code=*o.survey_code AND s.survey_option=o.survey_option "
> strSQL = strSQL & " AND s.survey_code=c.survey_code AND s.year=" &
> yearfield & " AND s.month=" & monthfield & " AND
> s.survey_code=" & question
>
>
> The asterisk is in line 4..
>
> Regards,
> Joe
>
>
> > replace + with *
>
> ex:
>
> where table1.col1 = * table2.colo2
>
> -----Original Message-----
> From: Joe [mailto:bugmania76@y...]
> Sent: Monday, September 09, 2002 1:14 PM
> To: ASP Databases
> Subject: [asp_databases] Different syntax for SQL?
>
>
> Hi guys, i wanted to display all record using sql in
> regardless of whether
> it is empty, In oracle sql, i could use the outerjoin (+) in
> the Where
> clause but it seems that access sql does not support this function.
> Is there a replacement for it?
>
> Thanks,
> Joe
>
Message #6 by "Peter Foti (PeterF)" <PeterF@S...> on Mon, 9 Sep 2002 11:54:35 -0400
|
|
Samir,
The example you provided is not a left outer join. It's an inner join.
Pete
> -----Original Message-----
> From: Samir Chitkara , Gurgaon [mailto:chitkaras@g...]
> Sent: Monday, September 09, 2002 11:52 AM
> To: ASP Databases
> Subject: [asp_databases] RE: Different syntax for SQL?
>
>
> hi!
>
> use left outer join
>
>
>
> SELECT products.prod_id, orders.price
> FROM orders INNER JOIN products ON
> orders.prod_id = products.prod_id
>
> -----Original Message-----
> From: Peter Foti (PeterF) [mailto:PeterF@S...]
> Sent: Monday, September 09, 2002 9:17 PM
> To: ASP Databases
> Subject: [asp_databases] RE: Different syntax for SQL?
>
>
> Use the following syntax:
>
> SELECT {[table.]field1 [AS alias1] [, [table.]field2 [AS
> alias2] [, ...]]}
> FROM table1 [ LEFT | RIGHT ] JOIN table2
> ON table1.field1 compopr table2.field2
> [WHERE... ]
>
> It looks like you are trying to setup an alias to the table
> name. Can't do
> that. So I would suggest modifying the SELECT portion to use
> the full table
> names and change your FROM clause. You will also create your
> JOIN in the
> FROM clause, not the WHERE clause.
>
> Hope this helps.
>
> Peter
>
>
>
> > -----Original Message-----
> > From: Joe [mailto:bugmania76@y...]
> > Sent: Monday, September 09, 2002 4:13 PM
> > To: ASP Databases
> > Subject: [asp_databases] RE: Different syntax for SQL?
> >
> >
> > Humm..i've tried that but it seems that it still have syntax
> > error, this
> > is wat i've written:
> >
> > strSQL = "SELECT s.counter,s.survey_code, s.survey_option,
> > o.option_description, c.code_description FROM survey_stat s"
> > strSQL = strSQL & ",survey_option o,survey_code c WHERE
> > s.survey_code=*o.survey_code AND s.survey_option=o.survey_option "
> > strSQL = strSQL & " AND s.survey_code=c.survey_code AND s.year=" &
> > yearfield & " AND s.month=" & monthfield & " AND
> > s.survey_code=" & question
> >
> >
> > The asterisk is in line 4..
> >
> > Regards,
> > Joe
> >
> >
> > > replace + with *
> >
> > ex:
> >
> > where table1.col1 = * table2.colo2
> >
> > -----Original Message-----
> > From: Joe [mailto:bugmania76@y...]
> > Sent: Monday, September 09, 2002 1:14 PM
> > To: ASP Databases
> > Subject: [asp_databases] Different syntax for SQL?
> >
> >
> > Hi guys, i wanted to display all record using sql in
> > regardless of whether
> > it is empty, In oracle sql, i could use the outerjoin (+) in
> > the Where
> > clause but it seems that access sql does not support this function.
> > Is there a replacement for it?
> >
> > Thanks,
> > Joe
> >
>
>
>
Message #7 by "Samir Chitkara , Gurgaon" <chitkaras@g...> on Mon, 9 Sep 2002 21:26:07 +0530
|
|
ya !!!
u can repalce it with left outer join..
> SELECT products.prod_id, orders.price
> FROM orders left outer join products ON
> orders.prod_id = products.prod_id
-----Original Message-----
From: Peter Foti (PeterF) [mailto:PeterF@S...]
Sent: Monday, September 09, 2002 9:25 PM
To: ASP Databases
Subject: [asp_databases] RE: Different syntax for SQL?
Samir,
The example you provided is not a left outer join. It's an inner join.
Pete
> -----Original Message-----
> From: Samir Chitkara , Gurgaon [mailto:chitkaras@g...]
> Sent: Monday, September 09, 2002 11:52 AM
> To: ASP Databases
> Subject: [asp_databases] RE: Different syntax for SQL?
>
>
> hi!
>
> use left outer join
>
>
>
> SELECT products.prod_id, orders.price
> FROM orders INNER JOIN products ON
> orders.prod_id = products.prod_id
>
> -----Original Message-----
> From: Peter Foti (PeterF) [mailto:PeterF@S...]
> Sent: Monday, September 09, 2002 9:17 PM
> To: ASP Databases
> Subject: [asp_databases] RE: Different syntax for SQL?
>
>
> Use the following syntax:
>
> SELECT {[table.]field1 [AS alias1] [, [table.]field2 [AS
> alias2] [, ...]]}
> FROM table1 [ LEFT | RIGHT ] JOIN table2
> ON table1.field1 compopr table2.field2
> [WHERE... ]
>
> It looks like you are trying to setup an alias to the table
> name. Can't do
> that. So I would suggest modifying the SELECT portion to use
> the full table
> names and change your FROM clause. You will also create your
> JOIN in the
> FROM clause, not the WHERE clause.
>
> Hope this helps.
>
> Peter
>
>
>
> > -----Original Message-----
> > From: Joe [mailto:bugmania76@y...]
> > Sent: Monday, September 09, 2002 4:13 PM
> > To: ASP Databases
> > Subject: [asp_databases] RE: Different syntax for SQL?
> >
> >
> > Humm..i've tried that but it seems that it still have syntax
> > error, this
> > is wat i've written:
> >
> > strSQL = "SELECT s.counter,s.survey_code, s.survey_option,
> > o.option_description, c.code_description FROM survey_stat s"
> > strSQL = strSQL & ",survey_option o,survey_code c WHERE
> > s.survey_code=*o.survey_code AND s.survey_option=o.survey_option "
> > strSQL = strSQL & " AND s.survey_code=c.survey_code AND s.year=" &
> > yearfield & " AND s.month=" & monthfield & " AND
> > s.survey_code=" & question
> >
> >
> > The asterisk is in line 4..
> >
> > Regards,
> > Joe
> >
> >
> > > replace + with *
> >
> > ex:
> >
> > where table1.col1 = * table2.colo2
> >
> > -----Original Message-----
> > From: Joe [mailto:bugmania76@y...]
> > Sent: Monday, September 09, 2002 1:14 PM
> > To: ASP Databases
> > Subject: [asp_databases] Different syntax for SQL?
> >
> >
> > Hi guys, i wanted to display all record using sql in
> > regardless of whether
> > it is empty, In oracle sql, i could use the outerjoin (+) in
> > the Where
> > clause but it seems that access sql does not support this function.
> > Is there a replacement for it?
> >
> > Thanks,
> > Joe
> >
>
>
>
Message #8 by "Peter Foti (PeterF)" <PeterF@S...> on Mon, 9 Sep 2002 12:01:47 -0400
|
|
Samir,
That is also not the correct syntax. It's not "left outer join", it's just
"left join". The correct syntax (for your example) would be:
SELECT products.prod_id, orders.price
FROM orders LEFT JOIN products ON
orders.prod_id = products.prod_id
-Pete
> -----Original Message-----
> From: Samir Chitkara , Gurgaon [mailto:chitkaras@g...]
> Sent: Monday, September 09, 2002 11:56 AM
> To: ASP Databases
> Subject: [asp_databases] RE: Different syntax for SQL?
>
>
> ya !!!
>
> u can repalce it with left outer join..
>
> > SELECT products.prod_id, orders.price
> > FROM orders left outer join products ON
> > orders.prod_id = products.prod_id
> -----Original Message-----
> From: Peter Foti (PeterF) [mailto:PeterF@S...]
> Sent: Monday, September 09, 2002 9:25 PM
> To: ASP Databases
> Subject: [asp_databases] RE: Different syntax for SQL?
>
>
> Samir,
>
> The example you provided is not a left outer join. It's an
> inner join.
>
> Pete
>
>
> > -----Original Message-----
> > From: Samir Chitkara , Gurgaon [mailto:chitkaras@g...]
> > Sent: Monday, September 09, 2002 11:52 AM
> > To: ASP Databases
> > Subject: [asp_databases] RE: Different syntax for SQL?
> >
> >
> > hi!
> >
> > use left outer join
> >
> >
> >
> > SELECT products.prod_id, orders.price
> > FROM orders INNER JOIN products ON
> > orders.prod_id = products.prod_id
> >
> > -----Original Message-----
> > From: Peter Foti (PeterF) [mailto:PeterF@S...]
> > Sent: Monday, September 09, 2002 9:17 PM
> > To: ASP Databases
> > Subject: [asp_databases] RE: Different syntax for SQL?
> >
> >
> > Use the following syntax:
> >
> > SELECT {[table.]field1 [AS alias1] [, [table.]field2 [AS
> > alias2] [, ...]]}
> > FROM table1 [ LEFT | RIGHT ] JOIN table2
> > ON table1.field1 compopr table2.field2
> > [WHERE... ]
> >
> > It looks like you are trying to setup an alias to the table
> > name. Can't do
> > that. So I would suggest modifying the SELECT portion to use
> > the full table
> > names and change your FROM clause. You will also create your
> > JOIN in the
> > FROM clause, not the WHERE clause.
> >
> > Hope this helps.
> >
> > Peter
> >
> >
> >
> > > -----Original Message-----
> > > From: Joe [mailto:bugmania76@y...]
> > > Sent: Monday, September 09, 2002 4:13 PM
> > > To: ASP Databases
> > > Subject: [asp_databases] RE: Different syntax for SQL?
> > >
> > >
> > > Humm..i've tried that but it seems that it still have syntax
> > > error, this
> > > is wat i've written:
> > >
> > > strSQL = "SELECT s.counter,s.survey_code, s.survey_option,
> > > o.option_description, c.code_description FROM survey_stat s"
> > > strSQL = strSQL & ",survey_option o,survey_code c WHERE
> > > s.survey_code=*o.survey_code AND s.survey_option=o.survey_option "
> > > strSQL = strSQL & " AND s.survey_code=c.survey_code AND
> s.year=" &
> > > yearfield & " AND s.month=" & monthfield & " AND
> > > s.survey_code=" & question
> > >
> > >
> > > The asterisk is in line 4..
> > >
> > > Regards,
> > > Joe
> > >
> > >
> > > > replace + with *
> > >
> > > ex:
> > >
> > > where table1.col1 = * table2.colo2
> > >
> > > -----Original Message-----
> > > From: Joe [mailto:bugmania76@y...]
> > > Sent: Monday, September 09, 2002 1:14 PM
> > > To: ASP Databases
> > > Subject: [asp_databases] Different syntax for SQL?
> > >
> > >
> > > Hi guys, i wanted to display all record using sql in
> > > regardless of whether
> > > it is empty, In oracle sql, i could use the outerjoin (+) in
> > > the Where
> > > clause but it seems that access sql does not support this
> function.
> > > Is there a replacement for it?
> > >
> > > Thanks,
> > > Joe
> > >
> >
> >
> >
>
>
>
Message #9 by "Samir Chitkara , Gurgaon" <chitkaras@g...> on Mon, 9 Sep 2002 21:37:41 +0530
|
|
ok!! thanks :)
-----Original Message-----
From: Peter Foti (PeterF) [mailto:PeterF@S...]
Sent: Monday, September 09, 2002 9:32 PM
To: ASP Databases
Subject: [asp_databases] RE: Different syntax for SQL?
Samir,
That is also not the correct syntax. It's not "left outer join", it's just
"left join". The correct syntax (for your example) would be:
SELECT products.prod_id, orders.price
FROM orders LEFT JOIN products ON
orders.prod_id = products.prod_id
-Pete
> -----Original Message-----
> From: Samir Chitkara , Gurgaon [mailto:chitkaras@g...]
> Sent: Monday, September 09, 2002 11:56 AM
> To: ASP Databases
> Subject: [asp_databases] RE: Different syntax for SQL?
>
>
> ya !!!
>
> u can repalce it with left outer join..
>
> > SELECT products.prod_id, orders.price
> > FROM orders left outer join products ON
> > orders.prod_id = products.prod_id
> -----Original Message-----
> From: Peter Foti (PeterF) [mailto:PeterF@S...]
> Sent: Monday, September 09, 2002 9:25 PM
> To: ASP Databases
> Subject: [asp_databases] RE: Different syntax for SQL?
>
>
> Samir,
>
> The example you provided is not a left outer join. It's an
> inner join.
>
> Pete
>
>
> > -----Original Message-----
> > From: Samir Chitkara , Gurgaon [mailto:chitkaras@g...]
> > Sent: Monday, September 09, 2002 11:52 AM
> > To: ASP Databases
> > Subject: [asp_databases] RE: Different syntax for SQL?
> >
> >
> > hi!
> >
> > use left outer join
> >
> >
> >
> > SELECT products.prod_id, orders.price
> > FROM orders INNER JOIN products ON
> > orders.prod_id = products.prod_id
> >
> > -----Original Message-----
> > From: Peter Foti (PeterF) [mailto:PeterF@S...]
> > Sent: Monday, September 09, 2002 9:17 PM
> > To: ASP Databases
> > Subject: [asp_databases] RE: Different syntax for SQL?
> >
> >
> > Use the following syntax:
> >
> > SELECT {[table.]field1 [AS alias1] [, [table.]field2 [AS
> > alias2] [, ...]]}
> > FROM table1 [ LEFT | RIGHT ] JOIN table2
> > ON table1.field1 compopr table2.field2
> > [WHERE... ]
> >
> > It looks like you are trying to setup an alias to the table
> > name. Can't do
> > that. So I would suggest modifying the SELECT portion to use
> > the full table
> > names and change your FROM clause. You will also create your
> > JOIN in the
> > FROM clause, not the WHERE clause.
> >
> > Hope this helps.
> >
> > Peter
> >
> >
> >
> > > -----Original Message-----
> > > From: Joe [mailto:bugmania76@y...]
> > > Sent: Monday, September 09, 2002 4:13 PM
> > > To: ASP Databases
> > > Subject: [asp_databases] RE: Different syntax for SQL?
> > >
> > >
> > > Humm..i've tried that but it seems that it still have syntax
> > > error, this
> > > is wat i've written:
> > >
> > > strSQL = "SELECT s.counter,s.survey_code, s.survey_option,
> > > o.option_description, c.code_description FROM survey_stat s"
> > > strSQL = strSQL & ",survey_option o,survey_code c WHERE
> > > s.survey_code=*o.survey_code AND s.survey_option=o.survey_option "
> > > strSQL = strSQL & " AND s.survey_code=c.survey_code AND
> s.year=" &
> > > yearfield & " AND s.month=" & monthfield & " AND
> > > s.survey_code=" & question
> > >
> > >
> > > The asterisk is in line 4..
> > >
> > > Regards,
> > > Joe
> > >
> > >
> > > > replace + with *
> > >
> > > ex:
> > >
> > > where table1.col1 = * table2.colo2
> > >
> > > -----Original Message-----
> > > From: Joe [mailto:bugmania76@y...]
> > > Sent: Monday, September 09, 2002 1:14 PM
> > > To: ASP Databases
> > > Subject: [asp_databases] Different syntax for SQL?
> > >
> > >
> > > Hi guys, i wanted to display all record using sql in
> > > regardless of whether
> > > it is empty, In oracle sql, i could use the outerjoin (+) in
> > > the Where
> > > clause but it seems that access sql does not support this
> function.
> > > Is there a replacement for it?
> > >
> > > Thanks,
> > > Joe
> > >
> >
> >
> >
>
>
>
|
|
|
View Cart
