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.
| Author |
Topic |
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
 Posted - 2003-03-13 : 10:46:28
|
| I am trying to create a patient waiting list. My problem is that we see relatives together. What I have is a view which tells me the 'main' person waiting and another view that tells me the 'secondary' waiters i.e. the relatives that are to be seen with the main waiter. I want to join them together so that I can see relatives easily.For example we may have in the main waiters listPatient1,WaitingListID3,... (other patient details)Patient2,WaitingListID7,...Patient14,WaitingListID2etcIn the secondary waiters view we may havePatient12,WaitingListID3,...Patient13,WaitingListID3,...Patient200,WaitingListID7,...Patient120,WaitingListID30,...etcWhat I want is to produce a list that has the main waiter followed by the related secondary waiters, using the example above we might havePatient1,WaitingListID3,... Patient12,WaitingListID3,... Patient13,WaitingListID3,...Patient2,WaitingListID7,... Patient200,WaitingListID7,...Patient14,WaitingListID2Patient120,WaitingListID30,...etc.I get the feeling that this should be simple but I can't get my head around it. I realise I could union the two views and order by the waiting list ID (though this doesn't guarantee the 'main' waiter will be listed first) but I think this would run too slowly and was wondering if there was a neater methodThanks in advancesteve |
|
|
lee_h
Starting Member
36 Posts |
Posted - 2003-03-13 : 18:10:20
|
| if you've already created views, you could a 1 to the end of the main_waiters view and a 2 to the end of the secondary_waiters view so that you can ensure they come out in the correct order.i.e.create view main_waiter asselect patient, waitinglistid, 1 as sort_orderfrom tablecreate view secondary_waiters asselect patient, waitinglistid, 2 as sort_orderfrom table |
 |
|
|
|
|
|
|
|
Patiently Waiting