locked
problem with select within select RRS feed

  • Question

  • Hi

    I want to execute below query in ms-sql server 2000 but its giving errors.
    Can anybody help please.

    select * from ( select vRefTable from mainmenu where iMenuId=58 )
    where (select vFieldsName from TableField where iMenuId=58
    and bIsPrimary=1 and iTableFieldId=11) = 1


    Its showing below errors. your help is greatly appreciated.

    Server: Msg 156, Level 15, State 1, Line 2
    Incorrect syntax near the keyword 'where'.
    Server: Msg 170, Level 15, State 1, Line 3
    Line 3: Incorrect syntax near '='.


    Thanks
    Ansari
    Wednesday, September 24, 2008 6:30 AM

Answers

  •  

    Code Snippet

    select * from ( select vRefTable from mainmenu where iMenuId=58 )a  --need a table alias
    where (select vFieldsName from TableField where iMenuId=58
    and bIsPrimary=1 and iTableFieldId=11) = 1 

     

     

    from above code, it may make the error.

     

    where (select vFieldsName from TableField where iMenuId=58
    and bIsPrimary=1 and iTableFieldId=11)=1   --here,if return more value ,but a single value,so like below

     

     

    "more value" = "single value"  --this is a error

     

    so,i suggest that you adjust the code like below:

     

    Code Snippet
    select vRefTable
    from mainmenu
    where iMenuId=58
    where exists(
     select *
     from TableField
     where iMenuId=58
      and bIsPrimary=1
      and iTableFieldId=11
      and vFieldsName = 1)

     

     

    Hope this helps.

    Wednesday, September 24, 2008 6:52 AM
  •  

    ok

    try this

     

    Code Snippet

    select m.vRefTable,t.vFieldsName

    from mainmenu m inner join TableField t

    on m.iMenuId=t.iMenuId

    where m.iMenuId=58

    and t.bIsPrimary=1

    and t.iTableFieldId=11

     

     

    Wednesday, September 24, 2008 8:27 AM

All replies

  • Hi:

     

    You should like this:

     

    Code Snippet

    select * from ( select vRefTable from mainmenu where iMenuId=58 )a  --need a table alias
    where (select vFieldsName from TableField where iMenuId=58
    and bIsPrimary=1 and iTableFieldId=11) = 1

     

     

    Wednesday, September 24, 2008 6:44 AM
  •  

    Code Snippet

    select * from ( select vRefTable from mainmenu where iMenuId=58 )a  --need a table alias
    where (select vFieldsName from TableField where iMenuId=58
    and bIsPrimary=1 and iTableFieldId=11) = 1 

     

     

    from above code, it may make the error.

     

    where (select vFieldsName from TableField where iMenuId=58
    and bIsPrimary=1 and iTableFieldId=11)=1   --here,if return more value ,but a single value,so like below

     

     

    "more value" = "single value"  --this is a error

     

    so,i suggest that you adjust the code like below:

     

    Code Snippet
    select vRefTable
    from mainmenu
    where iMenuId=58
    where exists(
     select *
     from TableField
     where iMenuId=58
      and bIsPrimary=1
      and iTableFieldId=11
      and vFieldsName = 1)

     

     

    Hope this helps.

    Wednesday, September 24, 2008 6:52 AM
  •  

    Your query doesn't make any sense to me,

    why don't tell us what you are trying to achive with some sample data, thenwe canhelp you better.

    Wednesday, September 24, 2008 7:21 AM
  • Hi

    Your code is very helpfull, thanks for your help. But I am getting below error.

    Code Snippet

    Server: Msg 245, Level 16, State 1, Line 1Syntax error converting the varchar value 'iNewsId' to a column of data type int.


    I am using below code:

    Code Snippet
    elect * from ( select vRefTable from mainmenu where iMenuId=58)a

    where exists (select * from TableField where iMenuId=58
    and bIsPrimary=1 and iTableFieldId=11 and vfieldsname = 1)

    my tables structure is as follows:

    mainmenu--TableName

    iMenuId   vRefTable----ColoumnNames
    58            news   ---------Records

    TableField
    iMenuId     bIsPrimary    iTableFieldId  vFieldsName
    58                 1                  11             iNewsId

    news
    iNewsId          vTitle                            vUR
    1                  news1                  http://www.gmail.com

    Thanks
    Ansari
    Wednesday, September 24, 2008 7:31 AM
  • and what you trying to achive?

    your expected output.

     

     

    Wednesday, September 24, 2008 7:42 AM
  •  

    Try this

    Code Snippet

     

    select m.vRefTable

    from mainmenu m inner join TableField t

    on m.iMenuId=t.iMenuId

    where m.iMenuId=58

    and t.bIsPrimary=1

    and t.iTableFieldId=11

    and t.vFieldsName = 1

     

     

    Wednesday, September 24, 2008 7:45 AM
  • Hi Mangal Pardeshi

    Thanks for your reply. I appreciate your help, I am getting below error using your sent query.

    Server: Msg 245, Level 16, State 1, Line 1
    Syntax error converting the varchar value 'iNewsId' to a column of data type int.

    I have tried to analyse my requirement below:

    mainmenu--TableName
    iMenuId   vRefTable----ColoumnNames
    58            news   ---------Records

    TableField
    iMenuId     bIsPrimary    iTableFieldId  vFieldsName
    58                 1                         11             iNewsId

    news

    iNewsId          vTitle                            vUR
    1                  news1                  http://www.gmail.com

    I am storing tablenames in MainMenu in vRefTable coloumn and coloumn names in vFieldsName of  TableFields table.
    I want to extract tablename from MainMenu whose iMenuId=58 and coloumn name from TableField table whose iMenuId=58 and
    bIsPrimary=1 and iTableFieldId=11 i.e iNewsId. After this I will be having both table name and coloumn name, using this I want to extract data based on the table name & coloumn name which I am having.

    Thanks again
    Ansari
    Wednesday, September 24, 2008 8:17 AM
  •  

    ok

    try this

     

    Code Snippet

    select m.vRefTable,t.vFieldsName

    from mainmenu m inner join TableField t

    on m.iMenuId=t.iMenuId

    where m.iMenuId=58

    and t.bIsPrimary=1

    and t.iTableFieldId=11

     

     

    Wednesday, September 24, 2008 8:27 AM