wood burning stoves*
The moose likes Oracle/OAS and the fly likes SQL query help Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "SQL query help" Watch "SQL query help" New topic
Author

SQL query help

ashok mandala
Greenhorn

Joined: Nov 23, 2012
Posts: 9
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

Joined: Nov 23, 2012
Posts: 9
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
Bartender

Joined: Mar 28, 2008
Posts: 1056
    
  52

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.


    Tim Driven Development
    ashok mandala
    Greenhorn

    Joined: Nov 23, 2012
    Posts: 9
    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

    Joined: Nov 23, 2012
    Posts: 9
    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
    Bartender

    Joined: Mar 28, 2008
    Posts: 1056
        
      52

    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?
     
    GeeCON Prague 2014
     
    subject: SQL query help