Complex SELECT Statement Help

Hi All,

My sql is a little rusty, i ve been trying to do few things but still no luck. I m trying to query some data in one column based on certain . Here is my puzzle:

I have 7 tables: categories, characteristics, configs, rm_cat, rm_chars, rm_conf and rooms.

And here are the details on these tables:

- categories: {cat_id, cat_name}

- characteristics: {char_id, char}

- configs: {conf_id, conf}

- rm_cat: {room_id, cat_id}

- rm_chars: { room_id, char_id}

- rm_conf: {room_id, conf_id}

- room: {room_id. room_name}

I m trying to select a "room_name" based on a certain cat_id, char_id and conf_id and i don't know how to do this.

Sincerely,

Try this:

select room_name

from room

join rm_conf

on room.[room_id]= rm_conf.[room_id]

join configs

on rm_conf.[conf_id]= configs.[conf_id]

join rm_cat

on room.[room_id]= rm_cat.[room_id]

join rm_chars

on room.[room_id]= rm_chars.[room_id]

join categories

on categories.[cat_id]= rm_cat.[cat_id]

join characteristics

on rm_chars.[char_id]= charateristics.[char_id]

where categories.[cat_id]= @.Cat_ID

and characteristics.[char_id]= @.Char_ID

and configs.[conf_id]= @.Conf_ID

|||

Thanks mpateson ...

Could you please tell me why am i getting this error : "ORA - 01722: invalid number" when i try to apply this select statement in my gridview

<asp:GridViewID="GridView1"runat="server"AllowPaging="True"AllowSorting="True"

DataSourceID="search_result">

</asp:GridView>

<asp:SqlDataSourceID="search_result"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionStringR25 %>"

ProviderName="<%$ ConnectionStrings:ConnectionStringR25.ProviderName %>"SelectCommand="SELECT R25.ROOMS.ROOM_SHORT FROM R25.ROOMS INNER JOIN R25.RM_CONF ON R25.ROOMS.ROOM_ID = R25.RM_CONF.ROOM_ID INNER JOIN R25.CONFIGS ON R25.RM_CONF.CONF_ID = R25.CONFIGS.CONF_ID INNER JOIN R25.RM_CAT ON R25.ROOMS.ROOM_ID = R25.RM_CAT.ROOM_ID INNER JOIN R25.RM_CHARS ON R25.ROOMS.ROOM_ID = R25.RM_CHARS.ROOM_ID INNER JOIN R25.CATEGORIES ON R25.CATEGORIES.CAT_ID = R25.RM_CAT.CAT_ID INNER JOIN R25.CHARACTERISTICS ON R25.RM_CHARS.CHAR_ID = R25.CHARACTERISTICS.CHAR_ID WHERE (R25.CATEGORIES.CAT_ID = :CAT_ID) AND (R25.CHARACTERISTICS.CHAR_ID = :CHAR_ID) AND (R25.CONFIGS.CONF_ID = :CONF_ID)">

<SelectParameters>

<asp:ControlParameterControlID="RadioButtonList1"DefaultValue="NULL"Name="CAT_ID"

PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="CheckBoxList1"DefaultValue="NULL"Name="CHAR_ID"

PropertyName="SelectedValue"/>

<asp:ControlParameterControlID="DropDownList1"DefaultValue="NULL"Name="CONF_ID"

PropertyName="SelectedValue"/>

</SelectParameters>

</asp:SqlDataSource>

|||

I found this on google.

ORA-01722:invalid numberCause:The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates.Action:Check the character strings in the function or expression. Check that they contain only numbers, a sign, a decimal point, and the character "E" or "e" and retry the operation.