Allocating Users as per their Category

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Allocating Users as per their Category

Author

Topic

daipayan
Posting Yak Master

181 Posts

Posted - 2010-12-01 : 05:14:18

Dear All,

I had 3 tables: category; users; seat
In Category, columns are: ID; category
In Users, columns are: ID; users; catID(FK); rsvp
In Seat, columns are: ID; seat_no; catID(FK)
Now for example:
I have 3 Category:

---------------
ID | Category
---------------
 1 |  RED
 2 |  BLUE
 3 |  GREEN
---------------

I have 3 Users:

-------------------------
ID | Users | catID | rsvp
-------------------------
 1 | John  |  1    | YES
 2 | Jim   |  1    | NO
 3 | Rose  |  3    | YES
-------------------------

I have 3 Seat:

---------------------
ID | seat_no | catID
---------------------
 1 |   1     |  1
 2 |   2     |  1
 3 |   3     |  3
---------------------

Now I want a T-SQL, through which users seat should be allocated according to their catID and if their respective rsvp is YES. The moment some user rsvp changed from NO to YES, his/her should seat should be allocated according to catID and some user rsvp changed from YES to NO, his/her should seat should not be shown in seat allocation query.

Hope I can make you understand my query.

Regards,
Daipayan
Software Programmer

Application has reported a 'Not My Fault' in module KRNL.EXE in line 0200:103F

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-12-01 : 06:07:19

It is not easy to understand.
Maybe you can show us your wanted output in relation to your sample data?

No, you're never too old to Yak'n'Roll if you're too young to die.

daipayan
Posting Yak Master

181 Posts

Posted - 2010-12-01 : 06:43:31

Ok Sir,

The Output will be:
Output Table:

------------------------
Users | seat_no | catID
------------------------
John  |   1     |   1
Rose  |   3     |   3
------------------------

See here,

John & Seat No. 1 belongs to CatID = 1, so he got seat_no 1 as his rsvp = YES
Rose & Seat No. 3 belongs to CatID = 3, so she got seat_no 3 as her rsvp = YES
But, Jim rsvp = NO, so he did not get any seat, even if his catID = 1

Hope you understand the Output.

quote:
Originally posted by webfred

It is not easy to understand.
Maybe you can show us your wanted output in relation to your sample data?

No, you're never too old to Yak'n'Roll if you're too young to die.

Regards,
Daipayan
Software Programmer

Application has reported a 'Not My Fault' in module KRNL.EXE in line 0200:103F

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-12-01 : 07:24:37

What if Jim had rsvp = YES then how will you decide if he gets seat_no 1 or seat_no 2?

No, you're never too old to Yak'n'Roll if you're too young to die.

daipayan
Posting Yak Master

181 Posts

Posted - 2010-12-01 : 07:29:12

If JIM rsvp = YES, he should get next available seat_no of the same catID.

quote:
Originally posted by webfred

What if Jim had rsvp = YES then how will you decide if he gets seat_no 1 or seat_no 2?

No, you're never too old to Yak'n'Roll if you're too young to die.

Regards,
Daipayan
Software Programmer

Application has reported a 'Not My Fault' in module KRNL.EXE in line 0200:103F

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-12-01 : 08:04:31

Sorry but then I have no idea in this moment...I've never had a problem like this.
It is a many to many relation between the tables User and Seat and the assigning of each User to a different Seat should be done in an application that is not working set based...

But that is only my opinion and I am curious about what the other mates in this forum will say.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources