Query to obtain missing number

I've been trying to figure out how to create a query that would list the missing numbers between a high and low number for a field. For example, If I have the recordset below:

1
3
4
6
7
9

I'd like the resulting recordset to be:

2
5
8

Is there a way to achieve this? Thanks, Jason.Yes, there are several ways.

What have you covered so far in class?

-PatP|||In Class? I'm not taking a class. I know the programming language fairly well, I just cannot figure this one out. Can you give me a quick example? Thanks, Jason.|||There are multiple ways to do this. Probably the simplest is to create a "numbers" table with one row for every interesting (possible) value that a number might have. For a two byte integer, this range could be -32768 through 32767. Once you've got the numbers table, you can do a simple exists test, something like:SELECT n.val
FROM numbers AS n
WHERE NOT EXISTS (SELECT *
FROM myRecordset AS r
WHERE r.val = n.val)Of course you'd also need to limit the result to just the values of interest in this case (between the Min and Max values already in your recordset).

-PatP|||I had thought of this, the problem is, I cannot create another table. I'm using Foxpro with a proprietary program which will not allow non-program specific tables to be used in conjunction with it's own. I need to find a different way. Thanks for the post though!!|||I had thought of this, the problem is, I cannot create another table. I'm using Foxpro with a proprietary program which will not allow non-program specific tables to be used in conjunction with it's own. I need to find a different way. Thanks for the post though!!|||I had thought of this, the problem is, I cannot create another table. I'm using Foxpro with a proprietary program which will not allow non-program specific tables to be used in conjunction with it's own. I need to find a different way. Thanks for the post though!!|||I had thought of this, the problem is, I cannot create another table. I'm using Foxpro with a proprietary program which will not allow non-program specific tables to be used in conjunction with it's own. I need to find a different way. Thanks for the post though!!|||Does Foxpro support recursive queries? If so, you could recursively increment an integer up to some limit and exclude the non-qualifying rows.

Query to import all?

Hi all,

[SQL Server 2005]

I just moved to SQL 2005 developer edition & was trying to figure out how to import all objects (all tables, views & stored procedures ... are the main ones I need at the moment) into a DB from SQL 2k ?

There's an option to chose "Write Query to ..." in the Import Wizard, but I'm not good at all at SQL or is there an option/tool to help me out w/ this.

Could someone please point me in the right direction if this has been posted before, or if you have a Script that does this, thanks much appreciated.Use the > Tasks > Generate Scripts > method on the database node.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

You can also use Backup/Resotre method if you want exact replica of the SQL Server 2000 database.... Another option is Copy Database Wizards ...

Madhu

query syntax problems

I am working in vb6 and am new to database access. I can't seem to figure out the syntax for the following:

rs1.Open "select * from orders where category = '" & button & " and invoice = " & invoice & "' order by guest", db, adOpenStatic, adLockOptimistic

The above statement doesn't work. What am I doing wrong?

JerrybYour embedded quotes were misplaced. The SQL being run is like:

select * from orders where category = 'xxx and invoice = yyy ' order by guest

But you probably meant this:

select * from orders where category = 'xxx' and invoice = 'yyy' order by guest

Try this:

rs1.Open "select * from orders where category = '" & button & "' and invoice = '" & invoice & "' order by guest", db, adOpenStatic, adLockOptimistic

(This is assuming that both button and invoice are strings rather than numbers - for numbers, you don't need the quotes.)

Hint: it's a good idea to print the SQL that is failing to see if it makes sense - e.g.:

Response.Write "select * from orders where category = '" & button & " and invoice = " & invoice & "' order by guest"

Query Syntax Help

I have a query that I am running out of sql server 2000 that is pulling duplicate records. I can probably figure it out but I am wondering if someone could look at it and point out errors in my syntax and/or structure.

Thanks!

code:------------------------
SELECT p.ParticipantID, pr.RaceID, p.FirstName, p.LastName, pr.Bib, p.Gender, pr.Age,
pr.AgeGrp, p.DOB, p.Address, p.City, p.St, p.Zip, pr.Clyde, pr.WhlChr, pr.RcWlk,
p.Phone, p.Email, reg.ShrtSize, reg.ShrtStyle, reg.WhereReg, reg.DateReg, reg.AmtPd
FROM Participant p INNER JOIN PartReg reg ON p.ParticipantID = reg.ParticipantID JOIN PartRace pr
ON pr.ParticipantID = p.ParticipantID JOIN RaceData rd ON pr.RaceID = rd.RaceID
WHERE (rd.EventID = 45 AND pr.RaceID = reg.RaceID) ORDER BY p.LastName
------------------------what is the error you are getting?|||you have 4 tables, but what are the relationships and their cardinalities?

for example, Participant--PartReg is probably 1--m, but what about the others?

somewhere along the line you have two unrelated relationships giving you a cross join effect|||I am not getting an error, it is just pulling duplicate fields.

In the Participant table, ParticipantID is a Primary Key and in PartRace and PartReg tables it is a Foreign Key. In the RaceData table, RaceID is a Primary Key and in PartRace and PartReg it is a Foreign Key.

Hope this helps~|||Assuming your data is clean, I believe your selection list is misleading.
You are not pulling any colums of "rd" (RaceData) table.
Perhaps there is a 1-n realtion (participants has entered in more than one races ?). Selelect the PK of RaceData along, this may shed some light.


SELECT
p.ParticipantID,
pr.RaceID,
p.FirstName,
p.LastName,
pr.Bib,
p.Gender,
pr.Age,
pr.AgeGrp,
p.DOB,
p.Address,
p.City,
p.St,
p.Zip,
pr.Clyde,
pr.WhlChr,
pr.RcWlk,
p.Phone,
p.Email,
reg.ShrtSize,
reg.ShrtStyle,
reg.WhereReg,
reg.DateReg,
reg.AmtPd,
rd.RaceID
FROM Participant p
INNER JOIN
PartReg reg
ON p.ParticipantID = reg.ParticipantID
JOIN
PartRace pr
ON pr.ParticipantID = p.ParticipantID
JOIN
RaceData rd
ON pr.RaceID = rd.RaceID
WHERE
(
rd.EventID = 45
AND pr.RaceID = reg.RaceID
)
ORDER BY p.LastName|||Addendum: what is the relation/structure concerning "Eventid" / "RaceId" in RaceData ? does one RACE cover multiple EVENTS ?

You are joining via "EventId", but say PK is "RaceID" ?

...not seeing clear yet...|||Each event has one or more races. The EventID in Events is a primary key and in RaceData is a foreign key. The problem is that some people could have registered for multiple races hence they appear more than once in the PartRace and PartReg tables but only once in the Participant table. I am trying to identify all participants for all of the races in a specific event.|||I found the problem. It was an error in my code that was over-writing the raceid field with the most current one. The query was doing exactly what it was supposed to be doing. The data was corrupted but I have resolved it.

THANKS A TON FOR ALL OF YOUR HELP!