Post reply

how to SELECT 'unassigned' elements

  • 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

  • July 31, 2003 at 5:50 pm

    #468115

    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

  • July 31, 2003 at 7:15 pm

    #468122

    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

  • July 31, 2003 at 7:29 pm

    #468124

    VB I'll accept. Thanks.

    But swan draught is a threat.

    Cheers,

    - Mark


    Cheers,
    - Mark

  • August 13, 2003 at 3:53 am

    #469700

    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

  • August 13, 2003 at 4:57 am

    #469708

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

  • August 13, 2003 at 6:03 am

    #469715

    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

  • August 13, 2003 at 10:42 am

    #469785

    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

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

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