Professional Web Applications Themes

Union as subquery problem - Microsoft SQL / MS SQL Server

I am trying to use a union query inside a subquery and failed : Here is what I am trying to do: Select PolicyID from Policies where PolicyID in (SELECT PolicyID From AlertPolicy UNION SELECT PolicyID From BackupPolicy ) This alone is working : Select PolicyID from Policies where PolicyID in (SELECT PolicyID From AlertPolicy) Any suggestion?...

  1. #1

    Default Union as subquery problem

    I am trying to use a union query inside a subquery and failed :
    Here is what I am trying to do:
    Select PolicyID from Policies where PolicyID in (SELECT PolicyID From
    AlertPolicy UNION SELECT PolicyID From BackupPolicy )

    This alone is working :
    Select PolicyID from Policies where PolicyID in (SELECT PolicyID From
    AlertPolicy)

    Any suggestion?


    Dror Guest

  2. #2

    Default Re: Union as subquery problem

    Thanks Delbert,

    I am using Access 2000, The error msg I've got is :
    "The operation is not allowed Inside a subquery" howevwer,
    Your second suggestion worked wonderful.

    I have already solved it by another elegant way:
    Create a view for the complex UNION subquery and then your query is much
    simple:

    SELECT PolicyID
    FROM Policies
    WHERE PolicyID in (Select PolicyID from view)

    Thanks anyway;

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Dror Guest

  3. #3

    Default Re: Union as subquery problem

    What is the problem? it should work. What error are you getting?

    --
    -Vishal
    Dror Bargil <com> wrote in message
    news:phx.gbl... 


    Vishal Guest

  4. #4

    Default Re: Union as subquery problem

    If there's any problems with this query you may replace it with

    Select PolicyID from Policies where PolicyID in (SELECT PolicyID From
    AlertPolicy) OR PolicyID in ( SELECT PolicyID From BackupPolicy )

    or even

    Select PolicyID from Policies where PolicyID in (SELECT PolicyID From
    AlertPolicy)
    UNION
    Select PolicyID from Policies where PolicyID in ( SELECT PolicyID From
    BackupPolicy )

    It's all will be the same

    "Dror Bargil" <com> wrote in message
    news:phx.gbl... 


    Alex Guest

Similar Threads

  1. Best way to use subquery?
    By greg.scharlemann@gmail.com in forum MySQL
    Replies: 4
    Last Post: November 27th, 01:25 AM
  2. Access Union Query in CF--Problem
    By weezerboy in forum Coldfusion Database Access
    Replies: 7
    Last Post: November 9th, 01:53 AM
  3. correlated subquery problem
    By SethosLA@gmail.com in forum MySQL
    Replies: 3
    Last Post: October 9th, 07:41 PM
  4. mySQL subquery problem
    By rmorgan in forum Coldfusion Database Access
    Replies: 7
    Last Post: October 30th, 07:06 AM
  5. How use Subquery better ???
    By lubiel in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 23rd, 07:47 PM

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