SQL query help

[ashok mandala]

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]

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]

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]

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]

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]

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.

select distinct m.menu_title, i.menu_item_label, a.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 a.read_only='Y'

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?