Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2002
    Location
    Kyiv, Ukraine
    Posts
    77

    Unanswered: SELECT on SELECT?

    Was trying to do some qurey like:

    SELECT * FROM (SELECT id FROM some_table)

    In Access it works smoothly but in MySQL gives a error. Is it forbidden to use several SELECTs in MySQL or what? Could you share your experience how do you avoid this bone in the neck? Temporary tables? Any other way?

    As far as I see the the solution to my problem now is a bit of DEnormalization of my database ...

    But maybe there are some civilized way of doing this?

    TIA.
    Yours faithfully,
    Yaroslav Zaremba

  2. #2
    Join Date
    Jan 2003
    Location
    Paris, France
    Posts
    320

    Re: SELECT on SELECT?

    Originally posted by aZa
    Was trying to do some qurey like:

    SELECT * FROM (SELECT id FROM some_table)

    In Access it works smoothly but in MySQL gives a error. Is it forbidden to use several SELECTs in MySQL or what? Could you share your experience how do you avoid this bone in the neck? Temporary tables? Any other way?

    As far as I see the the solution to my problem now is a bit of DEnormalization of my database ...

    But maybe there are some civilized way of doing this?

    TIA.
    Sub select like this is not permited in MySql
    You can create a temporary table to do this

    Code:
    create temporary table tmp_table type=heap SELECT id FROM some_table;
    
    select * from tmp_table ....;
    drop tmp_table;
    Olivier Miossec
    --
    http://www.lasso-developpeur.net/
    --

  3. #3
    Join Date
    Sep 2002
    Location
    Kyiv, Ukraine
    Posts
    77
    Do creating of temporary tables much load MySQL so that I'd better avoid using them or this is a common thing there and no problem at all with them? (considering resources it takes for creating a table and then for destroying it simply to make some query, isn't this a waste of resources or it's ok and usual thing for MySQL? ...)
    Yours faithfully,
    Yaroslav Zaremba

  4. #4
    Join Date
    Jan 2003
    Posts
    1

    Re: SELECT on SELECT?

    Originally posted by omiossec
    Sub select like this is not permited in MySql
    You can create a temporary table to do this

    Code:
    create temporary table tmp_table type=heap SELECT id FROM some_table;
    
    select * from tmp_table ....;
    drop tmp_table;
    Omiossec (or anyone), could you show us the whole bit to translate the original sub select?

    How do we actually use the stuff in tmp_table?

    cheers
    pat

  5. #5
    Join Date
    Nov 2002
    Posts
    5

  6. #6
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    482
    Originally posted by damonnew
    MySQL 4.1 does it.

    http://dev.mysql.com/doc/mysql/en/Nu..._features.html

    Download it here: http://dev.mysql.com/downloads/mysql/4.1.html
    But keep in mind 4.1 is not stable version

Posting Permissions

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