Complicated Statement

right, i need help with an SQL statement. been struggling with this and posted a few questions in the PHP section, but it does come down to the SQL statement in the end.

to keep it simple, if in my database i have numerous rows that all have the following fields: autoInc (auto incremented), name, id.


auto Inc name id
1 Bob 1001
2 Sue 1002
3 Jim 1005
4 Joe 1009
5 Bill 1002
...etc.


from here, i'm going to run a for loop that will search all numbers starting at "1001", right up to "9999"


for ($i=1001;$i<=9999;$i++) {

etc.

}


as each $i variable is accounted for (1001,1002,1003,1004,1005... etc.), I want to use a statement to search the "id" field in my database - and if $i is NOT found, i want it to...


echo $i.'<br />';


...which with the above example will look something like...


1003
1004
1006
1007
1008
1010...etc.


The "id" represents which category each person falls into, so there will be some with the same "id" (Bob & Bill), hence why it's not the auto-incremented field.

Can I write basic statements... yes. Do I get confused when they get more complicated and involve IN and BETWEEN? Very much so.

I know it can be done, but just need help with the syntax.

Many thanks...

:thumbsup:

No, not complicated, at all. Honest, about as simple as SQL queries get.

SELECT DISTINCT id
FROM yourtablename
WHERE id BETWEEN 1001 AND 9999
ORDER BY id


You don't even need to use BETWEEN, by the by:

SELECT DISTINCT id
FROM yourtablename
WHERE id >= 1001 AND id <= 9999
ORDER BY id

or, 100% equivalent,

SELECT DISTINCT id
FROM yourtablename
WHERE id > 1000 AND id < 10000
ORDER BY id

(Assuming, of course, the id is an integer field.)