Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    6

    Unanswered: WHERE in UNION Syntax?

    Hello,
    I have a problem. Maybe someone can help me...:

    Can I use a WHERE in a UNION Syntax?. Something like this:

    $query = "(SELECT Name, Autor, Typ, Dateiname FROM dokumente)
    UNION
    (SELECT at.Arbeitsname, ut.Name, at.Type, at.filename
    FROM users_table ut, arbeiten_table at
    WHERE at.AuthorID=ut.ID)
    WHERE Name = some_name";


    This doesn't work for me. If this is not possible, how can I get a similar result?

    Thanx and greetings,

    Nacho

  2. #2
    Join Date
    Apr 2003
    Location
    Edison, NJ / Oakland, NJ (Work)
    Posts
    32
    When you say it doesn't work do you mean that you get errors or that you don't get the result set that you expected?
    -----------------------------------
    Smith Hayward
    -----------------------------------

  3. #3
    Join Date
    Nov 2003
    Posts
    6
    I get an error

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if it were me doing it, i would simply put the WHERE condition inside the UNION, in each subselect

    SELECT Name, Autor, Typ, Dateiname
    FROM dokumente
    WHERE Name = some_name
    UNION
    SELECT at.Arbeitsname, ut.Name, at.Type, at.filename
    FROM users_table ut
    INNER JOIN arbeiten_table at
    ON at.AuthorID=ut.ID
    WHERE at.Arbeitsname= some_name

    if you really want to add the WHERE clause to the outside of the union, you may need to write it like this --

    SELECT *
    FROM (

    SELECT Name, Autor, Typ, Dateiname FROM dokumente)
    UNION
    SELECT at.Arbeitsname, ut.Name, at.Type, at.filename
    FROM users_table ut
    INNER JOIN arbeiten_table at
    ON at.AuthorID=ut.ID
    )
    WHERE Name = some_name


    rudy
    http://r937.com

  5. #5
    Join Date
    Nov 2003
    Posts
    6
    Great!
    Thank you very much for the help. I tried it and it worked.
    Greetings,

    Nacho

Posting Permissions

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