how to SELECT 'unassigned' elements

Question

how to SELECT 'unassigned' elements

yogiberr

Hall of Fame

Points: 3679
More actions
July 31, 2003 at 3:46 pm

#159530

Hi folks,
I have a 'store' table that has the following schema.(and a query that I initially thought would be a breeze to write :-))
tblStore
storeId IDENTITY PRIMARY KEY INT
storeNumber INT
storeName
At the moment I have 30 stores.
I would like to run a query that will:
1) set the MAX quantity of assignable numbers to 100.
2) return all the 'storeNumbers' that have not yet been assigned.
eg, if the 'storeNumbers' that are ALREADY assigned are from (30 to 60) then I would like to pull back the UNASSIGNED numbers, ie:
0,
1,
2,
etc,
up to 29...
then
61,
62,
63,
up to 100
I'm not sure how I would introduce the '100' into the query.
I realise that what I have so far is never going to do it.Can anyone out there give me a push?
Many thanks,
yogi
<code>
DECLARE @counter INT
SELECT @counter = 0
WHILE counter <101
BEGIN
WHERE NOT EXISTS (
SELECT DISTINCT storeNumber
FROM tblStore )
SET @counter = @counter + 1
END
<code>
Edited by - yogiberr on 07/31/2003 3:48:29 PM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

mccork SSC-Insane Points: 22288 More actions · Answer 1

Here's one way...

 

select Counter from

(SELECT DISTINCT storeNumber FROM tblStore) storeNumbers

RIGHT OUTER JOIN

(

    select n1*10 + n2 + 1 [Counter]

    from 

    (select 0 [n1] union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) list1

    cross join

    (select 0 [n2] union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) list2

) x

ON StoreNumber = Counter

WHERE StoreNumber is NULL

Cheers,

- Mark

Edited by - mccork on 07/31/2003 5:51:36 PM

Cheers,
- Mark

yogiberr Hall of Fame Points: 3679 More actions · Answer 2

utter joy.

the next time i'm back in oz with a bongo van, I'll shout you some swan draught/vb.

thanks man,

...from a kitesurfing yogiberr

mccork SSC-Insane Points: 22288 More actions · Answer 3

VB I'll accept. Thanks.

But swan draught is a threat.

Cheers,

- Mark

Cheers,
- Mark

yogiberr Hall of Fame Points: 3679 More actions · Answer 4

Howdy, sorry to keep bleeting on with this query, but I realised the other day the fact I have multiple clients will affect the query.ie, the actual "storeNumber" will not be unque in my tblStore, as several clients will have the same storeNumbers 🙁

my table schema is as follows:

tblStore

storeId PRIMARY KEY INT

storeNumber INT (the number that each client calls their store)

clientId FOREIGN KEY INT

storeName etc

So, I have to:

1) pass in a "@clientId" parameter to the sProc

2) retrieve all the unassigned storeNumbers on a PER CLIENT basis.

I have tried to add the following elements (which begin with "--", but I don't have a clue)

CREATE PROCEDURE [dbo].[spGetUnassignedStoreNumbers]

(

@clientId INT

)

AS

SELECT Counter

FROM (SELECT DISTINCT clientStoreNum FROM tblStore) storeNumbers RIGHT OUTER JOIN(SELECT n1*10 + n2 + 1 [Counter]

FROM (SELECT 0 [n1] UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) list1

cross join (SELECT 0 [n2] UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) list2) x

--ON clientStoreNum = Counter

--INNER JOIN ON tblStore.ClientId = tblClient.clientId

WHERE clientStoreNum is NULL

--AND tblStore.clientId = @clientId

GO

I have no idea how to do this.I realise that I cannot reference the "tblStore" because it is contained in nested queries.

Any help greatly appreciated, yours haplessly 🙂

yogi

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 5

I think changing "(SELECT DISTINCT clientStoreNum FROM tblStore Where clientId =@clientid)" from "(SELECT DISTINCT clientStoreNum FROM tblStore)" should do it

yogiberr Hall of Fame Points: 3679 More actions · Answer 6

hi bud, spot on, cheers.

I have noticed that the query will return 100 individual numbers, even if I supply a "clientId" (that does not actually exist in the database) as a param.

you supplied the following syntax:

SELECT DISTINCT clientStoreNum

FROM tblStore

WHERE clientId =@clientid

It worked fine.I changed it to the following, to try to make it return "nothing" if the clientId does not exist in the database.

SELECT DISTINCT clientStoreNum

FROM tblStore s, tblClient c

WHERE s.clientId =@clientid

AND c.clientId =s.clientId

Is this because the "clientStoreNum" will ALWAYS be NULL if I pass in a non-existent clientId as a param?

sorry for asking AGAIN :-),

thanks,

yogi

yogiberr Hall of Fame Points: 3679 More actions · Answer 7

howdy,

I managed to get this to work, probably dodgy, but it seems OK

I added this to the top of the query:

IF EXISTS

(SELECT 1

FROM tblCLient c, tblStore s

WHERE s.clientId = @clientId

AND s.clientId = c.clientId)

BEGIN

(hopefully, this query has been put to bed 🙂

many thanks folks,

yogiberr