Professional Web Applications Themes

sytax problem - Microsoft SQL / MS SQL Server

I'm not allowed to use outer join operators in a query containing joined tables. Any suggestions? select u.FIRSTNAME First_Name, u.LASTNAME Last_Name, count (*) Objects_Published, ui1.INFO City, ui2.INFO Organization_Name, ui3.INFO Organization_Type from Users u, USER_INFO ui1, USER_INFO ui2, USER_INFO ui3 join VIEW_OBJECTS o on o.CREATEDBY = u.USERID and o.COMPANYID=11 and u.USERID*=ui1.USERID and u.USERID*=ui2.USERID and u.USERID*=ui3.USERID and ui1.TYPE='City' and ui1.TYPE='Organization Name' and ui1.TYPE='Organization Type' group by u.FIRSTNAME, u.LASTNAME -- Thank you, Andrew Dunbar...

  1. #1

    Default sytax problem

    I'm not allowed to use outer join operators in a query containing joined
    tables. Any suggestions?

    select
    u.FIRSTNAME First_Name,
    u.LASTNAME Last_Name,
    count (*) Objects_Published,
    ui1.INFO City,
    ui2.INFO Organization_Name,
    ui3.INFO Organization_Type
    from
    Users u,
    USER_INFO ui1,
    USER_INFO ui2,
    USER_INFO ui3
    join
    VIEW_OBJECTS o
    on
    o.CREATEDBY = u.USERID
    and o.COMPANYID=11
    and u.USERID*=ui1.USERID
    and u.USERID*=ui2.USERID
    and u.USERID*=ui3.USERID
    and ui1.TYPE='City'
    and ui1.TYPE='Organization Name'
    and ui1.TYPE='Organization Type'
    group by
    u.FIRSTNAME,
    u.LASTNAME

    --
    Thank you,

    Andrew Dunbar


    Andrew Guest

  2. #2

    Default Re: sytax problem

    Try:
    select
    u.FIRSTNAME First_Name,
    u.LASTNAME Last_Name,
    count (*) Objects_Published,
    ui1.INFO City,
    ui2.INFO Organization_Name,
    ui3.INFO Organization_Type
    from
    Users u
    left outer join USER_INFO ui1 on u.USERID=ui1.USERID
    left outer join USER_INFO ui2 on u.USERID=ui2.USERID
    left outer join USER_INFO ui3 on u.USERID=ui3.USERID
    left outer join VIEW_OBJECTS o on o.CREATEDBY = u.USERID
    where
    o.COMPANYID=11
    and ui1.TYPE='City'
    and ui1.TYPE='Organization Name'
    and ui1.TYPE='Organization Type'
    group by
    u.FIRSTNAME,
    u.LASTNAME

    --
    -Vishal
    Andrew Dunbar <com> wrote in message
    news:phx.gbl... 


    Vishal Guest

  3. #3

    Default Re: sytax problem

    correction:you may have to remove following columns from select
    ui1.INFO City,
    ui2.INFO Organization_Name,
    ui3.INFO Organization_Type
    because they are not part of group by clause.
    Either have them in group by clause / or dont include them in select.

    --
    -Vishal
    Vishal Parkar <com> wrote in message
    news:phx.gbl... 
    >
    >[/ref]


    Vishal Guest

Similar Threads

  1. Replies: 2
    Last Post: September 30th, 12:13 PM
  2. Replies: 0
    Last Post: August 23rd, 11:56 AM
  3. Replies: 0
    Last Post: August 2nd, 03:03 PM
  4. Replies: 2
    Last Post: July 17th, 07:27 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