A statement that returns what is and what is not in the database

Hi!!
I wonder if anyone can help. I have a select statement :e.g
select * from products
where productref in ('123456',
'123457',
'123458',
'123459'
and so on)
The products that are in the database come are returned as results.
So,
The query returns:
123456 - sadjsjfsdfjlksf
123457',- fdfslkdjfdj
123458',- dfsjflkdjlsjf
But I also need to find out what is not returned. There are about
10,000 lines and I need to find out what has not been returned. please
helpselect * from products
where productref NOT in ('123456',
'123457',
'123458',
'123459'
and so on)
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Himani" <himani77@.gmail.com> wrote in message
news:1171103166.734403.217840@.s48g2000cws.googlegroups.com...
> Hi!!
> I wonder if anyone can help. I have a select statement :e.g
> select * from products
> where productref in ('123456',
> '123457',
> '123458',
> '123459'
> and so on)
> The products that are in the database come are returned as results.
> So,
> The query returns:
> 123456 - sadjsjfsdfjlksf
> 123457',- fdfslkdjfdj
> 123458',- dfsjflkdjlsjf
> But I also need to find out what is not returned. There are about
> 10,000 lines and I need to find out what has not been returned. please
> help
>|||On 10 Feb, 12:49, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> select * from products
> where productref NOT in ('123456',
> '123457',
> '123458',
> '123459'
> and so on)
> --
> Hilary Cotter
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com
> "Himani" <himan...@.gmail.com> wrote in message
> news:1171103166.734403.217840@.s48g2000cws.googlegroups.com...
>
> > Hi!!
> > I wonder if anyone can help. I have a select statement :e.g
> > select * from products
> > where productref in ('123456',
> > '123457',
> > '123458',
> > '123459'
> > and so on)
> > The products that are in the database come are returned as results.
> > So,
> > The query returns:
> > 123456 - sadjsjfsdfjlksf
> > 123457',- fdfslkdjfdj
> > 123458',- dfsjflkdjlsjf
> > But I also need to find out what is not returned. There are about
> > 10,000 lines and I need to find out what has not been returned. please
> > help- Hide quoted text -
> - Show quoted text -
I did try this but it did not work. the query returned other products.
perhaps I can explain a bit more.
The key thing is that some of the products are not on the database at
all.The List
'123456',
123457',
'123458',
'123459'
is from another system in which all the products are there. Whilst the
select query returns
the top 4 products as they are in the database. I want another query
that returns the ones that are not in the database at all - i.e. it
should return 123459 etc but it doesn't.|||> I want another query
> that returns the ones that are not in the database at all - i.e. it
> should return 123459 etc but it doesn't.
One method:
SELECT
OtherDatabaseList.productref
FROM
(SELECT '123456' AS productref
UNION ALL SELECT '123457'
UNION ALL SELECT '123458'
UNION ALL SELECT '123459'
) OtherDatabaseList
WHERE NOT EXISTS
(
SELECT *
FROM dbo.products p
WHERE p.productref = OtherDatabaseList.productref
)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Himani" <himani77@.gmail.com> wrote in message
news:1171128176.411862.307420@.a75g2000cwd.googlegroups.com...
> On 10 Feb, 12:49, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
>> select * from products
>> where productref NOT in ('123456',
>> '123457',
>> '123458',
>> '123459'
>> and so on)
>> --
>> Hilary Cotter
>> Looking for a SQL Server replication
>> book?http://www.nwsu.com/0974973602.html
>> Looking for a FAQ on Indexing Services/SQL
>> FTShttp://www.indexserverfaq.com
>> "Himani" <himan...@.gmail.com> wrote in message
>> news:1171103166.734403.217840@.s48g2000cws.googlegroups.com...
>>
>> > Hi!!
>> > I wonder if anyone can help. I have a select statement :e.g
>> > select * from products
>> > where productref in ('123456',
>> > '123457',
>> > '123458',
>> > '123459'
>> > and so on)
>> > The products that are in the database come are returned as results.
>> > So,
>> > The query returns:
>> > 123456 - sadjsjfsdfjlksf
>> > 123457',- fdfslkdjfdj
>> > 123458',- dfsjflkdjlsjf
>> > But I also need to find out what is not returned. There are about
>> > 10,000 lines and I need to find out what has not been returned. please
>> > help- Hide quoted text -
>> - Show quoted text -
> I did try this but it did not work. the query returned other products.
> perhaps I can explain a bit more.
> The key thing is that some of the products are not on the database at
> all.The List
> '123456',
> 123457',
> '123458',
> '123459'
> is from another system in which all the products are there. Whilst the
> select query returns
> the top 4 products as they are in the database. I want another query
> that returns the ones that are not in the database at all - i.e. it
> should return 123459 etc but it doesn't.
>|||On 10 Feb, 19:42, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:
> > I want another query
> > that returns the ones that are not in the database at all - i.e. it
> > should return 123459 etc but it doesn't.
> One method:
> SELECT
> OtherDatabaseList.productref
> FROM
> (SELECT '123456' AS productref
> UNION ALL SELECT '123457'
> UNION ALL SELECT '123458'
> UNION ALL SELECT '123459'
> ) OtherDatabaseList
> WHERE NOT EXISTS
> (
> SELECT *
> FROM dbo.products p
> WHERE p.productref = OtherDatabaseList.productref
> )
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Himani" <himan...@.gmail.com> wrote in message
> news:1171128176.411862.307420@.a75g2000cwd.googlegroups.com...
>
> > On 10 Feb, 12:49, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> >> select * from products
> >> where productref NOT in ('123456',
> >> '123457',
> >> '123458',
> >> '123459'
> >> and so on)
> >> --
> >> Hilary Cotter
> >> Looking for a SQL Server replication
> >> book?http://www.nwsu.com/0974973602.html
> >> Looking for a FAQ on Indexing Services/SQL
> >> FTShttp://www.indexserverfaq.com
> >> "Himani" <himan...@.gmail.com> wrote in message
> >>news:1171103166.734403.217840@.s48g2000cws.googlegroups.com...
> >> > Hi!!
> >> > I wonder if anyone can help. I have a select statement :e.g
> >> > select * from products
> >> > where productref in ('123456',
> >> > '123457',
> >> > '123458',
> >> > '123459'
> >> > and so on)
> >> > The products that are in the database come are returned as results.
> >> > So,
> >> > The query returns:
> >> > 123456 - sadjsjfsdfjlksf
> >> > 123457',- fdfslkdjfdj
> >> > 123458',- dfsjflkdjlsjf
> >> > But I also need to find out what is not returned. There are about
> >> > 10,000 lines and I need to find out what has not been returned. please
> >> > help- Hide quoted text -
> >> - Show quoted text -
> > I did try this but it did not work. the query returned other products.
> > perhaps I can explain a bit more.
> > The key thing is that some of the products are not on the database at
> > all.The List
> > '123456',
> > 123457',
> > '123458',
> > '123459'
> > is from another system in which all the products are there. Whilst the
> > select query returns
> > the top 4 products as they are in the database. I want another query
> > that returns the ones that are not in the database at all - i.e. it
> > should return 123459 etc but it doesn't.- Hide quoted text -
> - Show quoted text -
hi !!
Thanks a lot for sql code. I understand the query but there is a
slight problem. I do not have the access to the other database. I
have been given a list of product refs between the range
114203-124675. Now this was an upload that wasn't done properly. So,
roughly 5241 products are on the system and 5233 are not. The products
that the database contains are easily obtainable by the following
query:
select * from products where productref in (114203,114204,114205 etc)
So this query returns 114203,114204 as they are in the database. But I
need to know is there a way of retrieving 114205 which is not on the
database. Any tips would be much appreciated.
Regards
Himani|||> Thanks a lot for sql code. I understand the query but there is a
> slight problem. I do not have the access to the other database. I
> have been given a list of product refs between the range
> 114203-124675. Now this was an upload that wasn't done properly. So,
> roughly 5241 products are on the system and 5233 are not. The products
> that the database contains are easily obtainable by the following
> query:
> select * from products where productref in (114203,114204,114205 etc)
> So this query returns 114203,114204 as they are in the database. But I
> need to know is there a way of retrieving 114205 which is not on the
> database. Any tips would be much appreciated.
So you need to identify the products that are in the list but not in the
database, right? I believe the query I originally posted will do that -
just specify the actual list of all products in the list you were given as
the derrived table. For example, I would expect the query below to return
only 114205 if 11403 and 11404 are in the products table.
SELECT
OtherDatabaseList.productref
FROM
(SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
) OtherDatabaseList
WHERE NOT EXISTS
(
SELECT *
FROM dbo.products p
WHERE p.productref = OtherDatabaseList.productref
)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Himani" <himani77@.gmail.com> wrote in message
news:1171190077.447791.188360@.q2g2000cwa.googlegroups.com...
> On 10 Feb, 19:42, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
> wrote:
>> > I want another query
>> > that returns the ones that are not in the database at all - i.e. it
>> > should return 123459 etc but it doesn't.
>> One method:
>> SELECT
>> OtherDatabaseList.productref
>> FROM
>> (SELECT '123456' AS productref
>> UNION ALL SELECT '123457'
>> UNION ALL SELECT '123458'
>> UNION ALL SELECT '123459'
>> ) OtherDatabaseList
>> WHERE NOT EXISTS
>> (
>> SELECT *
>> FROM dbo.products p
>> WHERE p.productref = OtherDatabaseList.productref
>> )
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Himani" <himan...@.gmail.com> wrote in message
>> news:1171128176.411862.307420@.a75g2000cwd.googlegroups.com...
>>
>> > On 10 Feb, 12:49, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
>> >> select * from products
>> >> where productref NOT in ('123456',
>> >> '123457',
>> >> '123458',
>> >> '123459'
>> >> and so on)
>> >> --
>> >> Hilary Cotter
>> >> Looking for a SQL Server replication
>> >> book?http://www.nwsu.com/0974973602.html
>> >> Looking for a FAQ on Indexing Services/SQL
>> >> FTShttp://www.indexserverfaq.com
>> >> "Himani" <himan...@.gmail.com> wrote in message
>> >>news:1171103166.734403.217840@.s48g2000cws.googlegroups.com...
>> >> > Hi!!
>> >> > I wonder if anyone can help. I have a select statement :e.g
>> >> > select * from products
>> >> > where productref in ('123456',
>> >> > '123457',
>> >> > '123458',
>> >> > '123459'
>> >> > and so on)
>> >> > The products that are in the database come are returned as results.
>> >> > So,
>> >> > The query returns:
>> >> > 123456 - sadjsjfsdfjlksf
>> >> > 123457',- fdfslkdjfdj
>> >> > 123458',- dfsjflkdjlsjf
>> >> > But I also need to find out what is not returned. There are about
>> >> > 10,000 lines and I need to find out what has not been returned.
>> >> > please
>> >> > help- Hide quoted text -
>> >> - Show quoted text -
>> > I did try this but it did not work. the query returned other products.
>> > perhaps I can explain a bit more.
>> > The key thing is that some of the products are not on the database at
>> > all.The List
>> > '123456',
>> > 123457',
>> > '123458',
>> > '123459'
>> > is from another system in which all the products are there. Whilst the
>> > select query returns
>> > the top 4 products as they are in the database. I want another query
>> > that returns the ones that are not in the database at all - i.e. it
>> > should return 123459 etc but it doesn't.- Hide quoted text -
>> - Show quoted text -
> hi !!
> Thanks a lot for sql code. I understand the query but there is a
> slight problem. I do not have the access to the other database. I
> have been given a list of product refs between the range
> 114203-124675. Now this was an upload that wasn't done properly. So,
> roughly 5241 products are on the system and 5233 are not. The products
> that the database contains are easily obtainable by the following
> query:
> select * from products where productref in (114203,114204,114205 etc)
> So this query returns 114203,114204 as they are in the database. But I
> need to know is there a way of retrieving 114205 which is not on the
> database. Any tips would be much appreciated.
> Regards
> Himani
>|||On 11 Feb, 15:25, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:
> > Thanks a lot for sql code. I understand the query but there is a
> > slight problem. I do not have the access to the other database. I
> > have been given a list of product refs between the range
> > 114203-124675. Now this was an upload that wasn't done properly. So,
> > roughly 5241 products are on the system and 5233 are not. The products
> > that the database contains are easily obtainable by the following
> > query:
> > select * from products where productref in (114203,114204,114205 etc)
> > So this query returns 114203,114204 as they are in the database. But I
> > need to know is there a way of retrieving 114205 which is not on the
> > database. Any tips would be much appreciated.
> So you need to identify the products that are in the list but not in the
> database, right? I believe the query I originally posted will do that -
> just specify the actual list of all products in the list you were given as
> the derrived table. For example, I would expect the query below to return
> only 114205 if 11403 and 11404 are in the products table.
> SELECT
> OtherDatabaseList.productref
> FROM
> (SELECT '114203' AS productref
> UNION ALL SELECT '114204'
> UNION ALL SELECT '114205'
> ) OtherDatabaseList
> WHERE NOT EXISTS
> (
> SELECT *
> FROM dbo.products p
> WHERE p.productref = OtherDatabaseList.productref
> )
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Himani" <himan...@.gmail.com> wrote in message
> news:1171190077.447791.188360@.q2g2000cwa.googlegroups.com...
>
> > On 10 Feb, 19:42, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
> > wrote:
> >> > I want another query
> >> > that returns the ones that are not in the database at all - i.e. it
> >> > should return 123459 etc but it doesn't.
> >> One method:
> >> SELECT
> >> OtherDatabaseList.productref
> >> FROM
> >> (SELECT '123456' AS productref
> >> UNION ALL SELECT '123457'
> >> UNION ALL SELECT '123458'
> >> UNION ALL SELECT '123459'
> >> ) OtherDatabaseList
> >> WHERE NOT EXISTS
> >> (
> >> SELECT *
> >> FROM dbo.products p
> >> WHERE p.productref = OtherDatabaseList.productref
> >> )
> >> --
> >> Hope this helps.
> >> Dan Guzman
> >> SQL Server MVP
> >> "Himani" <himan...@.gmail.com> wrote in message
> >>news:1171128176.411862.307420@.a75g2000cwd.googlegroups.com...
> >> > On 10 Feb, 12:49, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> >> >> select * from products
> >> >> where productref NOT in ('123456',
> >> >> '123457',
> >> >> '123458',
> >> >> '123459'
> >> >> and so on)
> >> >> --
> >> >> Hilary Cotter
> >> >> Looking for a SQL Server replication
> >> >> book?http://www.nwsu.com/0974973602.html
> >> >> Looking for a FAQ on Indexing Services/SQL
> >> >> FTShttp://www.indexserverfaq.com
> >> >> "Himani" <himan...@.gmail.com> wrote in message
> >> >>news:1171103166.734403.217840@.s48g2000cws.googlegroups.com...
> >> >> > Hi!!
> >> >> > I wonder if anyone can help. I have a select statement :e.g
> >> >> > select * from products
> >> >> > where productref in ('123456',
> >> >> > '123457',
> >> >> > '123458',
> >> >> > '123459'
> >> >> > and so on)
> >> >> > The products that are in the database come are returned as results.
> >> >> > So,
> >> >> > The query returns:
> >> >> > 123456 - sadjsjfsdfjlksf
> >> >> > 123457',- fdfslkdjfdj
> >> >> > 123458',- dfsjflkdjlsjf
> >> >> > But I also need to find out what is not returned. There are about
> >> >> > 10,000 lines and I need to find out what has not been returned.
> >> >> > please
> >> >> > help- Hide quoted text -
> >> >> - Show quoted text -
> >> > I did try this but it did not work. the query returned other products.
> >> > perhaps I can explain a bit more.
> >> > The key thing is that some of the products are not on the database at
> >> > all.The List
> >> > '123456',
> >> > 123457',
> >> > '123458',
> >> > '123459'
> >> > is from another system in which all the products are there. Whilst the
> >> > select query returns
> >> > the top 4 products as they are in the database. I want another query
> >> > that returns the ones that are not in the database at all - i.e. it
> >> > should return 123459 etc but it doesn't.- Hide quoted text -
> >> - Show quoted text -
> > hi !!
> > Thanks a lot for sql code. I understand the query but there is a
> > slight problem. I do not have the access to the other database. I
> > have been given a list of product refs between the range
> > 114203-124675. Now this was an upload that wasn't done properly. So,
> > roughly 5241 products are on the system and 5233 are not. The products
> > that the database contains are easily obtainable by the following
> > query:
> > select * from products where productref in (114203,114204,114205 etc)
> > So this query returns 114203,114204 as they are in the database. But I
> > need to know is there a way of retrieving 114205 which is not on the
> > database. Any tips would be much appreciated.
> > Regards
> > Himani- Hide quoted text -
> - Show quoted text -
Many Thanks. It did work. Just wondering if you could just explain the
scrip a bit if you had some time or comment it.
Regards
Himani|||> Many Thanks. It did work. Just wondering if you could just explain the
> scrip a bit if you had some time or comment it.
There are a few different techniques to get the desired list but all require
that you have a table that contains the complete list of products that
should be in the database. You could either create and load a table with
those products or use the derived table (inline view). I chose a derived
table because this was one-time query.
To expand derived tables, start with a query that returns the desired
result:
SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
Then enclose it in parenthesis and specify an alias:
(
SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
) AS OtherDatabaseList
You can then use it anywhere in a query where a normal table can be
specified:
SELECT *
FROM
(
SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
) AS OtherDatabaseList
Now that you have the list of what should be in the database, you can
identify the rows that are not in the target table using one of several
techniques:
NOT EXISTS:
SELECT
OtherDatabaseList.productref
FROM
(SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
) OtherDatabaseList
WHERE NOT EXISTS
(
SELECT *
FROM dbo.products p
WHERE p.productref = OtherDatabaseList.productref
)
NOT IN:
SELECT
OtherDatabaseList.productref
FROM
(SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
) OtherDatabaseList
WHERE OtherDatabaseList.productref NOT IN
(
SELECT p.productref
FROM dbo.products p
)
Note that the NOT IN method is dangerous for nullable columns; no rows will
be returned if any NULL values are in the NOT IN list. I always use NOT
EXISTS instead to avoid that issue:
OUTER JOIN:
SELECT
OtherDatabaseList.productref
FROM
(SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
) OtherDatabaseList
LEFT OUTER JOIN dbo.products p ON
p.productref = OtherDatabaseList.productref
WHERE p.productref IS NULL
MINUS (SQL 2005):
SELECT
OtherDatabaseList.productref
FROM
(SELECT '114203' AS productref
UNION ALL SELECT '114204'
UNION ALL SELECT '114205'
) OtherDatabaseList
EXCEPT
SELECT p.productref FROM dbo.products p
Hope this helps.
Dan Guzman
SQL Server MVP
"Himani" <himani77@.gmail.com> wrote in message
news:1171296448.577247.172070@.l53g2000cwa.googlegroups.com...
> On 11 Feb, 15:25, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
> wrote:
>> > Thanks a lot for sql code. I understand the query but there is a
>> > slight problem. I do not have the access to the other database. I
>> > have been given a list of product refs between the range
>> > 114203-124675. Now this was an upload that wasn't done properly. So,
>> > roughly 5241 products are on the system and 5233 are not. The products
>> > that the database contains are easily obtainable by the following
>> > query:
>> > select * from products where productref in (114203,114204,114205 etc)
>> > So this query returns 114203,114204 as they are in the database. But I
>> > need to know is there a way of retrieving 114205 which is not on the
>> > database. Any tips would be much appreciated.
>> So you need to identify the products that are in the list but not in the
>> database, right? I believe the query I originally posted will do that -
>> just specify the actual list of all products in the list you were given
>> as
>> the derrived table. For example, I would expect the query below to
>> return
>> only 114205 if 11403 and 11404 are in the products table.
>> SELECT
>> OtherDatabaseList.productref
>> FROM
>> (SELECT '114203' AS productref
>> UNION ALL SELECT '114204'
>> UNION ALL SELECT '114205'
>> ) OtherDatabaseList
>> WHERE NOT EXISTS
>> (
>> SELECT *
>> FROM dbo.products p
>> WHERE p.productref = OtherDatabaseList.productref
>> )
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Himani" <himan...@.gmail.com> wrote in message
>> news:1171190077.447791.188360@.q2g2000cwa.googlegroups.com...
>>
>> > On 10 Feb, 19:42, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
>> > wrote:
>> >> > I want another query
>> >> > that returns the ones that are not in the database at all - i.e. it
>> >> > should return 123459 etc but it doesn't.
>> >> One method:
>> >> SELECT
>> >> OtherDatabaseList.productref
>> >> FROM
>> >> (SELECT '123456' AS productref
>> >> UNION ALL SELECT '123457'
>> >> UNION ALL SELECT '123458'
>> >> UNION ALL SELECT '123459'
>> >> ) OtherDatabaseList
>> >> WHERE NOT EXISTS
>> >> (
>> >> SELECT *
>> >> FROM dbo.products p
>> >> WHERE p.productref = OtherDatabaseList.productref
>> >> )
>> >> --
>> >> Hope this helps.
>> >> Dan Guzman
>> >> SQL Server MVP
>> >> "Himani" <himan...@.gmail.com> wrote in message
>> >>news:1171128176.411862.307420@.a75g2000cwd.googlegroups.com...
>> >> > On 10 Feb, 12:49, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
>> >> >> select * from products
>> >> >> where productref NOT in ('123456',
>> >> >> '123457',
>> >> >> '123458',
>> >> >> '123459'
>> >> >> and so on)
>> >> >> --
>> >> >> Hilary Cotter
>> >> >> Looking for a SQL Server replication
>> >> >> book?http://www.nwsu.com/0974973602.html
>> >> >> Looking for a FAQ on Indexing Services/SQL
>> >> >> FTShttp://www.indexserverfaq.com
>> >> >> "Himani" <himan...@.gmail.com> wrote in message
>> >> >>news:1171103166.734403.217840@.s48g2000cws.googlegroups.com...
>> >> >> > Hi!!
>> >> >> > I wonder if anyone can help. I have a select statement :e.g
>> >> >> > select * from products
>> >> >> > where productref in ('123456',
>> >> >> > '123457',
>> >> >> > '123458',
>> >> >> > '123459'
>> >> >> > and so on)
>> >> >> > The products that are in the database come are returned as
>> >> >> > results.
>> >> >> > So,
>> >> >> > The query returns:
>> >> >> > 123456 - sadjsjfsdfjlksf
>> >> >> > 123457',- fdfslkdjfdj
>> >> >> > 123458',- dfsjflkdjlsjf
>> >> >> > But I also need to find out what is not returned. There are about
>> >> >> > 10,000 lines and I need to find out what has not been returned.
>> >> >> > please
>> >> >> > help- Hide quoted text -
>> >> >> - Show quoted text -
>> >> > I did try this but it did not work. the query returned other
>> >> > products.
>> >> > perhaps I can explain a bit more.
>> >> > The key thing is that some of the products are not on the database
>> >> > at
>> >> > all.The List
>> >> > '123456',
>> >> > 123457',
>> >> > '123458',
>> >> > '123459'
>> >> > is from another system in which all the products are there. Whilst
>> >> > the
>> >> > select query returns
>> >> > the top 4 products as they are in the database. I want another query
>> >> > that returns the ones that are not in the database at all - i.e. it
>> >> > should return 123459 etc but it doesn't.- Hide quoted text -
>> >> - Show quoted text -
>> > hi !!
>> > Thanks a lot for sql code. I understand the query but there is a
>> > slight problem. I do not have the access to the other database. I
>> > have been given a list of product refs between the range
>> > 114203-124675. Now this was an upload that wasn't done properly. So,
>> > roughly 5241 products are on the system and 5233 are not. The products
>> > that the database contains are easily obtainable by the following
>> > query:
>> > select * from products where productref in (114203,114204,114205 etc)
>> > So this query returns 114203,114204 as they are in the database. But I
>> > need to know is there a way of retrieving 114205 which is not on the
>> > database. Any tips would be much appreciated.
>> > Regards
>> > Himani- Hide quoted text -
>> - Show quoted text -
> Many Thanks. It did work. Just wondering if you could just explain the
> scrip a bit if you had some time or comment it.
> Regards
> Himani
>