• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL query help

 
ashok mandala
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have been trying to figure out the solution.... but got stuck some where


i have 3 tables menus , menu_items, menu _access

teh below are the columns in the tables

menus.MENU_ID, menus.MENU_LABEL, menus.MENU_TITLE, menus.DISPLAY_ON_MENU, menus.MENU_ORDER, menus.APPLICATION_TYPE, menus.CREATE_USER, menus.CREATE_DATE, menus.UPDATE_USER, menus.UPDATE_DATE, menus.SSPMD_AUDIT_ID


menu_items.MENU_ID, menu_items.MENU_ITEM_ID, menu_items.MENU_ITEM_LABEL, menu_items.MENU_ITEM_ORDER, menu_items.OBJECT, menu_items.DESCRIPTION, menu_items.DISPLAY, menu_items.DEFAULT_HELP_TEXT, menu_items.CUSTOM_HELP_TEXT, menu_items.IS_HTML, menu_items.CREATE_USER, menu_items.CREATE_DATE, menu_items.UPDATE_USER, menu_items.UPDATE_DATE, menu_items.SSPMD_AUDIT_ID

menu_access.ROLE_SEQ, menu_access.MENU_ID, menu_access.MENU_ITEM_ID, menu_access.READ_ONLY, menu_access.CREATE_USER, menu_access.CREATE_DATE, menu_access.UPDATE_USER, menu_access.UPDATE_DATE, menu_access.SSPMD_AUDIT_ID.

And i am trying to write the query for the following

Find the unique menu title and menu item label for having access ready_only


help me
 
ashok mandala
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
menus
=============
MENU_ID, MENU_LABEL, MENU_TITLE

menu_items
=============
MENU_ID, MENU_ITEM_ID, MENU_ITEM_LABEL

menu_access
============
ROLE_SEQ, MENU_ID, MENU_ITEM_ID, READ_ONLY


these are the tables i am using.............
 
Tim Cooke
Sheriff
Pie
Posts: 3133
136
Clojure IntelliJ IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Show us how you are getting on with it so far.
  • Do you have an idea of how you'd achieve this?
  • Have you tried any SQL statements yet? If so what were they and what issues did you encounter?


  • There are many folks here willing to help you but we are NotACodeMill (<--click) and you are expected to ShowSomeEffort (<--click) first.
     
    ashok mandala
    Greenhorn
    Posts: 9
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    The below is what i have done till now

    select distinct(menu_title) from(select menu_title, menu_item_label item, read_only
    from sa_menus sm, sa_menu_items smi, sa_menu_access sma
    where sm.menu_id=SMI.MENU_ID and sma.menu_id in
    (select menu_id from sa_menu_access where read_only='Y') )


    but i want to display 3 columns

    menu_title menu_item_label Read_only
    ------------------------------------------------------

    How to retrieve all the values, and i am getting duplicate menu_title values where i need to retrieve unique menu_title
     
    ashok mandala
    Greenhorn
    Posts: 9
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Hi i got the answer finally
    select distinct menu_title, menu_item_label, read_only from
    sa_menus m, sa_menu_items i, sa_menu_access a
    where m.menu_id=i.menu_id
    and m.menu_id=a.menu_id
    and i.menu_id=a.menu_id
    and read_only='Y'

    and my question is can we still optimize this query are this is the best?

    thanks & regards
    Ashok
     
    Tim Cooke
    Sheriff
    Pie
    Posts: 3133
    136
    Clojure IntelliJ IDE Java
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    You don't need the third table join as two joins is enough for three tables. It is also good practice to prefix your select columns with the table name or alias.



    As far as performance goes, it's a pretty simple query so doubt there's much to be done. By all means test the performance in the context of your application but unless you identify it as a bottleneck then I would leave it alone.

    Lastly, when posting code please UseCodeTags (<-- click). See how much nicer your sql code looks when I use them?
     
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic