Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161

    Unanswered: sub-SELECT to return two columns

    Code:
    SELECT t1.`ID`,
    (SELECT f1 FROM `tbl2` t2 WHERE t2.`ID` = t1.`ID`) AS `f1`,
    (SELECT f2 FROM `tbl2` t2 WHERE t2.`ID` = t1.`ID`) AS `f2`,
    t1.`c1`,
    t1.`c2`,
    FROM `tbl1` t1
    Any way to join the 2 sub-selects ?
    Code:
    (SELECT f1, f2 FROM `tbl2` t2 WHERE t2.`ID` = t1.`ID`) AS `f1`, `f2`,
    Trying to save CPU by reducing sub-SELECT queries here because both are from the same table tbl2.

    Thanks
    Last edited by anjanesh; 09-12-07 at 05:07.
    MySQL 5.1

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Not sure why you need a sub-select at all:
    Code:
    SELECT t1.ID, t2.f1, t2.f2, t1.c1, t1.c2
    FROM tbl1 t1
    LEFT OUTER JOIN tbl2 t2 on (t2.id = t1.id)

  3. #3
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Also worth noting that a JOIN will be backwardly compatible with older versions of MySQL, whereas subselect is version 4.1 onwards...

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by aschk
    whereas subselect is version 4.1 onwards...
    Who would use such a version that doesn't even support sub-selects...

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by shammat
    Who would use such a version that doesn't even support sub-selects...
    someone who pre-paid $27 for seven years of hosting on 3.23 and does not want to lose the investment

    as russell peters would say, "YOU GO TO JAIL BAD BOY"

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161
    Im using MySQL 5.0.x and not interested in backward compatibility for this.
    Thought about JOINs but for another script where I used JOIN, it took seconds to load - sometimes even minutes.

    Guess I'll fallback to JOINs but is there no workaround using a single sub-query ?
    MySQL 5.1

  7. #7
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by anjanesh
    Thought about JOINs but for another script where I used JOIN, it took seconds to load - sometimes even minutes.
    Then you'll need to check your indexes.
    Usually joins are a lot faster than sub-selects (especially sub-selects in the SELECT list)

  8. #8
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Perhaps you should perform an EXPLAIN on your SQL query(s) to find where indexes should be put.

    And there is NO workaround for it. They can only ever return one row and value inside the SELECT clause.

Posting Permissions

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