Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2006
    Posts
    19

    Unanswered: View with From in subquery

    I have a query from which I want to turn into a view:

    SELECT * FROM `telefoonnummers`
    WHERE `Afdeling` IN
    (SELECT `Afdeling`
    FROM
    (
    SELECT @a:=@a+1 as 'a'
    ,`Afdeling`
    FROM `Afdeling-telefoon`
    JOIN (SELECT @a:=0) x
    ORDER BY `Afdeling`
    ) y WHERE mod(a,2) = 0) ORDER BY `Afdeling`, `Naam`

    The only problem is I cant make a view out of this. It says it is not possible.

    It returns:
    #1349 - View's SELECT contains a subquery in the FROM clause

    Any ideas how I could turn this into a view (or stored procedure maybe)?

  2. #2
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    34
    Quote Originally Posted by jacsoft
    I have a query from which I want to turn into a view:

    SELECT * FROM `telefoonnummers`
    WHERE `Afdeling` IN
    (SELECT `Afdeling`
    FROM
    (
    SELECT @a:=@a+1 as 'a'
    ,`Afdeling`
    FROM `Afdeling-telefoon`
    JOIN (SELECT @a:=0) x
    ORDER BY `Afdeling`
    ) y WHERE mod(a,2) = 0) ORDER BY `Afdeling`, `Naam`

    The only problem is I cant make a view out of this. It says it is not possible.

    It returns:
    #1349 - View's SELECT contains a subquery in the FROM clause

    Any ideas how I could turn this into a view (or stored procedure maybe)?
    Instead of turning it into a view, turn it into a stored procedure (MySQL 5.0.0 or higher). It can still return the resultset you want to see, and it shouldn't complain about the subquery.
    DigiOz Multimedia
    http://www.digioz.com

  3. #3
    Join Date
    Jan 2006
    Posts
    19
    I use MySQL > 5 so stored procedures shouldn't be a problem. The only problem is that I don't know whether the application that I use which requires this query is capable of calling a Stored procedure instead of a query, so therefore I rather use a view/query.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    could you explain in words what the "application that I use which requires this query" expects to see

    what is that stuff with the variable and the mod function and all that?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    34
    Quote Originally Posted by jacsoft
    I use MySQL > 5 so stored procedures shouldn't be a problem. The only problem is that I don't know whether the application that I use which requires this query is capable of calling a Stored procedure instead of a query, so therefore I rather use a view/query.
    If you are using a .NET application I can tell from experience that calling a stored procedure is not a problem. Unless you are using some other type of application, in which case I would have to ask what it is.
    DigiOz Multimedia
    http://www.digioz.com

Posting Permissions

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