File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
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


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
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: 845
    
  42

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: 845
        
      42

    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?
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: SQL query help
     
    Similar Threads
    ArrayList storing only last table record
    sqlexception with hsqldb-please help
    select and update using the same statement object
    How to convert from sql to xml
    Resultset updateRow() throws exception for row with auto generated key