Order using SQL

Results 1 to 2 of 2

Thread: Order using SQL

  1. #1
    Join Date
    Dec 1969

    Default Order using SQL

    I&#039;m using a .csv file to generate a dynamic menu.<BR>The file in part looks like this:<BR><BR>ParentMenu,KeyValue,Position,Descript or<BR>0, 40, 1, Racquets<BR>0, 41, 2, Apparel<BR>0, 42, 3, Bags<BR>40, 50, 1, Performance<BR>40, 51, 2, Recreational<BR>40, 52, 3, Junior<BR>41, 54, 1, Bottoms-Pants<BR>41, 55, 2, Bottoms-Shorts<BR>...<BR><BR>Items with a ParentMenu value of 0 are main menu items<BR>Then those ParentMenu values that match the keyvalue of those main menu items are submenus.<BR><BR>So the menu should look something like this:<BR>Racquets<BR>* &nbsp; Performance<BR>* &nbsp; Recreational<BR>* &nbsp; Junior<BR>Apparel<BR>* &nbsp; Bottom-Pants<BR>* &nbsp; Bottom-Shorts<BR>...<BR><BR>Is there an SQL statement I can use that will sort the whole menu in one shot? If not, any other suggestions?<BR><BR>Thanks<BR>Marcel<BR>

  2. #2
    Join Date
    Dec 1969

    Default In general, no...

    ...but if you *know* that you will not go more than two levels deep (that is, there is nothing "under" Racquets/Performance), then you can do something like:<BR><BR>SELECT * FROM table ORDER BY ( IIF( ParentMenu=0, KeyValue, ParentMenu ) ), KeyValue<BR><BR>You *could* make it a simpler if you would assign a ParentMenu value equal to the KeyValue when you are creating a "top level" item. Do you see that? Then just<BR><BR>SELECT * FROM table ORDER BY ParentMenu, KeyValue<BR><BR>will work!<BR><BR>*********<BR><BR>If you need to go multi-levels...well, there isn&#039;t any general SQL solution using the data as you have presented it to yourself.<BR><BR>There are many hairy solutions, but I don&#039;t think you can justify them when using a simple .CSV file. One thing to think about: If you know you only need *three* levels, add a "grandParent" field. Do it right, and you will just have<BR><BR>ORDER BY grandParent, parentMenu, keyValue<BR><BR>Etc. Put in a field for every level and it works. Not advisable for something like a messageboard, of course, but for something like this...why not?<BR><BR>Oh...and then to *display* the data:<BR><BR>http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=154<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts