Professional Web Applications Themes

Simple Select Statement - Microsoft SQL / MS SQL Server

Hello, I have the following Select statement ( how do I make it more complex ? just kidding..) select * from ScopeContent Where ((scope = 0 and ItemID = ItemID) or (scope=1 and DeptID = DeptID) or (scope=3 and CompanyID = CompanyID)) The Select statement above is good if I place DeptID and ComapnyID in the ScopeContent Table I know that the ScopeContent is related to Dept_Table and Dept_Table is related to Comp_Table How do I change the above statement by not adding the fields ( DeptID and CompanyID) to the ScopeContent Table there is no reason to have them ...

  1. #1

    Default Simple Select Statement

    Hello,


    I have the following Select statement ( how do I make it more complex ?
    just kidding..)

    select * from ScopeContent
    Where ((scope = 0 and ItemID = ItemID) or (scope=1 and DeptID = DeptID) or
    (scope=3 and CompanyID = CompanyID))

    The Select statement above is good if I place DeptID and ComapnyID in the
    ScopeContent Table

    I know that the ScopeContent is related to Dept_Table and Dept_Table is
    related to Comp_Table

    How do I change the above statement by not adding the fields ( DeptID and
    CompanyID) to the ScopeContent Table

    there is no reason to have them there repeated all the time wast of space

    on the otherhand may be it is faster and the select statement is easier

    What do you think?

    Thank you for your help


    CSharp Guest

  2. #2

    Default Re: Simple Select Statement

    Pls provide DDLs and sample data (INSERT statements) inorder to understand
    the problem properly and write proper query.
    The solution can be achieved by joining ScopeContent ,Dept_Table and
    Comp_Table tables.

    HTH,
    Srinivas Sampangi
    ..



    "CSharp ( ILM )" <com> wrote in message
    news:#phx.gbl... 
    or 


    sampangi Guest

  3. #3

    Default Re: Simple Select Statement

    How do you use Union or Union all
    Can you please provide a simple example
    Thank you for your input


    "Uri Dimant" <co.il> wrote in message
    news:%phx.gbl... 
    index 
    needs. [/ref]
    DeptID) [/ref]
    the [/ref]
    and [/ref]
    space 
    >
    >[/ref]


    CSharp Guest

  4. #4

    Default Re: Simple Select Statement

    CSharp
    Perfoming such logical operation in WHERE caluse as OR you may lose index
    usage on your query.
    I'd go to re-write the query with UNION or UNION ALL depends on your needs.






    "CSharp ( ILM )" <com> wrote in message
    news:#phx.gbl... 
    or 


    Uri Guest

  5. #5

    Default Re: Simple Select Statement

    Sure

    SELECT column_name1, column_name2
    FROM table_name1
    WHERE column_name1 = some_value
    UNION
    SELECT column_name1, column_name2
    FROM table_name1
    WHERE column_name2 = some_value

    Note: I like to say "It depends" ,so........









    "CSharp ( ILM )" <com> wrote in message
    news:phx.gbl... 
    > index 
    > needs. [/ref][/ref]
    ? [/ref]
    > DeptID) [/ref]
    > the [/ref][/ref]
    is [/ref]
    > and [/ref]
    > space [/ref][/ref]
    easier 
    > >
    > >[/ref]
    >
    >[/ref]


    Uri Guest

  6. #6

    Default Re: Simple Select Statement

    Thank you
    I tried it. In simple ways it works
    I some ways I get errors. don't remember the error at this time
    Thank you for your help


    "Uri Dimant" <co.il> wrote in message
    news:phx.gbl... 
    > > index 
    > > needs. [/ref][/ref]
    complex 
    > > DeptID) [/ref][/ref]
    in [/ref]
    > is [/ref][/ref]
    DeptID 
    > > space [/ref]
    > easier 
    > >
    > >[/ref]
    >
    >[/ref]


    CSharp Guest

Similar Threads

  1. SELECT statement
    By Simon in forum Microsoft SQL / MS SQL Server
    Replies: 23
    Last Post: August 18th, 01:12 PM
  2. Need help with select statement
    By Don in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 23rd, 04:53 AM
  3. Need some help on a SELECT statement
    By Sydney in forum Microsoft SQL / MS SQL Server
    Replies: 5
    Last Post: July 16th, 01:42 PM
  4. Replies: 0
    Last Post: July 13th, 11:41 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139