|
 |
asp_databases thread: "sql syntax problem"
Message #1 by "taherm@f... on Tue, 21 Aug 2001 12:23:24
|
|
SELECT stock.itemcode
FROM prod right outer JOIN stock ON prod.Key = stock.itemcode;
the above sql syntax retrieves all the records which are present in prod
and stock table but ignore any records which are present in stock but not
in prod.
for example if prod has 2 records called a and b
stock has a , b, and c
this query outputs only a and b
what i need is to output only c.
in other words i need the sql syntax to check in prod and stock table and
out only those from stock whih is not present in prod.
Its really very very urgent. can anyone help me.
thanks
Message #2 by "Matteo Lonardi" <mlonardi@h...> on Tue, 21 Aug 2001 13:56:34 +0200
|
|
Try:
Select * From stock Where not exists (
SELECT stock.itemcode
FROM prod right outer JOIN stock ON prod.Key = stock.itemcode)
----- Original Message -----
From: <taherm@f...>
To: "ASP Databases" <asp_databases@p...>
Sent: Tuesday, August 21, 2001 1:35 PM
Subject: [asp_databases] "sql syntax problem"
> SELECT stock.itemcode
> FROM prod right outer JOIN stock ON prod.Key = stock.itemcode;
>
> the above sql syntax retrieves all the records which are present in prod
> and stock table but ignore any records which are present in stock but not
> in prod.
> for example if prod has 2 records called a and b
> stock has a , b, and c
> this query outputs only a and b
>
> what i need is to output only c.
> in other words i need the sql syntax to check in prod and stock table and
> out only those from stock whih is not present in prod.
>
> Its really very very urgent. can anyone help me.
> thanks
Message #3 by "Drew, Ron" <RDrew@B...> on Tue, 21 Aug 2001 14:34:59 -0400
|
|
select stock.itemcode from stock
where stock.itemcode not in
(select prod.key from prod)
-----Original Message-----
From: taherm@f...
[mailto:taherm@f...]
Sent: Tuesday, August 21, 2001 7:35 AM
To: ASP Databases
Subject: [asp_databases] "sql syntax problem"
SELECT stock.itemcode
FROM prod right outer JOIN stock ON prod.Key =3D stock.itemcode;
the above sql syntax retrieves all the records which are present in
prod
and stock table but ignore any records which are present in stock but
not
in prod.
for example if prod has 2 records called a and b
stock has a , b, and c
this query outputs only a and b
what i need is to output only c.
in other words i need the sql syntax to check in prod and stock table
and
out only those from stock whih is not present in prod.
Its really very very urgent. can anyone help me.
thanks
Message #4 by "Drew, Ron" <RDrew@B...> on Tue, 21 Aug 2001 14:36:30 -0400
|
|
I just responded with "not in"....doesn't not exists stop at the first
itemcode not in prod
-----Original Message-----
From: Matteo Lonardi [mailto:mlonardi@h...]
Sent: Tuesday, August 21, 2001 7:57 AM
To: ASP Databases
Subject: [asp_databases] Re: "sql syntax problem"
Try:
Select * From stock Where not exists (
SELECT stock.itemcode
FROM prod right outer JOIN stock ON prod.Key =3D stock.itemcode)
----- Original Message -----
From: <taherm@f...>
To: "ASP Databases" <asp_databases@p...>
Sent: Tuesday, August 21, 2001 1:35 PM
Subject: [asp_databases] "sql syntax problem"
> SELECT stock.itemcode
> FROM prod right outer JOIN stock ON prod.Key =3D stock.itemcode;
>
> the above sql syntax retrieves all the records which are present in
prod
> and stock table but ignore any records which are present in stock but
not
> in prod.
> for example if prod has 2 records called a and b
> stock has a , b, and c
> this query outputs only a and b
>
> what i need is to output only c.
> in other words i need the sql syntax to check in prod and stock
table and
> out only those from stock whih is not present in prod.
>
> Its really very very urgent. can anyone help me.
> thanks
Message #5 by "Ken Schaefer" <ken@a...> on Wed, 22 Aug 2001 12:02:16 +1000
|
|
AFAIK NOT EXISTS is the better way to do this.
Drew, you're right - it does stop when it finds the first match - but for
each record in the "outer" query. IE, for each record in the outer query, it
stops evaluating the inner query as soon as it finds a match.
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
----- Original Message -----
From: "Drew, Ron" <RDrew@B...>
To: "ASP Databases" <asp_databases@p...>
Sent: Wednesday, August 22, 2001 4:36 AM
Subject: [asp_databases] Re: "sql syntax problem"
I just responded with "not in"....doesn't not exists stop at the first
itemcode not in prod
-----Original Message-----
From: Matteo Lonardi [mailto:mlonardi@h...]
Sent: Tuesday, August 21, 2001 7:57 AM
To: ASP Databases
Subject: [asp_databases] Re: "sql syntax problem"
Try:
Select * From stock Where not exists (
SELECT stock.itemcode
FROM prod right outer JOIN stock ON prod.Key = stock.itemcode)
----- Original Message -----
From: <taherm@f...>
To: "ASP Databases" <asp_databases@p...>
Sent: Tuesday, August 21, 2001 1:35 PM
Subject: [asp_databases] "sql syntax problem"
> SELECT stock.itemcode
> FROM prod right outer JOIN stock ON prod.Key = stock.itemcode;
>
> the above sql syntax retrieves all the records which are present in prod
> and stock table but ignore any records which are present in stock but not
> in prod.
> for example if prod has 2 records called a and b
> stock has a , b, and c
> this query outputs only a and b
>
> what i need is to output only c.
> in other words i need the sql syntax to check in prod and stock table and
> out only those from stock whih is not present in prod.
>
> Its really very very urgent. can anyone help me.
> thanks
---
* Fast, Full-Featured Microsoft® Excel Web Reports & Charts!
A breakthrough in high performance Web application development, SoftArtisans
ExcelWriter 1.1 supports native Excel charting, image insertion, and
advanced functions & formatting. One click generates presentation-quality
Excel spreadsheets-and ExcelWriter performs over 100 times faster than the
Excel Object. Several editions, including ExcelWriterFREE, are available.
http://www.softartisans.com/softartisans/excelwriter.html>
Message #6 by mmcpheat@s... on Wed, 22 Aug 2001 09:58:30 +0800
|
|
SELECT stock.itemcode
FROM stock left outer JOIN prod ON stock.itemcode =3D prod.Key
WHERE prod.Key IS NULL
-----Original Message-----
From: taherm@f...
[mailto:taherm@f...]
Sent: Tuesday, 21 August 2001 7:35 PM
To: ASP Databases
Subject: [asp_databases] "sql syntax problem"
SELECT stock.itemcode
FROM prod right outer JOIN stock ON prod.Key =3D stock.itemcode;
the above sql syntax retrieves all the records which are present in
prod
and stock table but ignore any records which are present in stock but
not
in prod.
for example if prod has 2 records called a and b
stock has a , b, and c
this query outputs only a and b
what i need is to output only c.
in other words i need the sql syntax to check in prod and stock table
and
out only those from stock whih is not present in prod.
Its really very very urgent. can anyone help me.
thanks
Message #7 by Kyle Burns <kburns@c...> on Fri, 24 Aug 2001 14:50:49 -0500
|
|
Will this work?
SELECT key
FROM prod
WHERE key NOT IN (SELECT itemcode FROM stock)
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
=3D=3D=3D=3D=3D=3D=3D=3D=3D
Kyle M. Burns, MCSD
ECommerce Technology Manager
Centra Credit Union
kburns@c...
-----Original Message-----
From: taherm@f...
[mailto:taherm@f...]
Sent: Tuesday, August 21, 2001 6:35 AM
To: ASP Databases
Subject: [asp_databases] "sql syntax problem"
SELECT stock.itemcode
FROM prod right outer JOIN stock ON prod.Key =3D stock.itemcode;
the above sql syntax retrieves all the records which are present in
prod
and stock table but ignore any records which are present in stock but
not
in prod.
for example if prod has 2 records called a and b
stock has a , b, and c
this query outputs only a and b
what i need is to output only c.
in other words i need the sql syntax to check in prod and stock table
and
out only those from stock whih is not present in prod.
Its really very very urgent. can anyone help me.
thanks
|
|
|
View Cart
