SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Jan 2005
    Location
    mikkeli
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Difficult MySQL question about Rewriting Subqueries as Joins

    In my portal I use MySQL version 4.1.7 where i can use subquerys but now i need to move my
    portal to server where is MySQL version 3.23.58.

    This SQL statement should convert to form where is NO subquerys and do it only ONE statement:


    select * from ajanvaraus where toimenpidetunnus in(select toimenpidetunnus from toimenpide where kategorianimi = (select distinct kategorianimi from toimenpide where toimenpidetunnus = $tp))";


    I managed to convert inner subquery to form:

    select taulu2.toimenpidetunnus from toimenpide as taulu1, toimenpide as taulu2 where taulu1.toimenpidetunnus = $tp and taulu1.kategorianimi = taulu2.kategorianimi

    now i need to compine my last two querys togehter... but how???

    Tiina from Finland,

    p.s sorry my bad english

  2. #2
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    Írebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I must admit that this is a wild guess...

    Code:
    SELECT t1.* FROM ajanvaraus as t1
      INNER JOIN toimenpide as t2
         ON t2.toimenpidetunnus = $tp
      INNER JOIN toimenpide as t3
         ON t3.toimenpidetunnus
          = t1.toimenpidetunnus
        AND t3.kategorianimi
          = t2.kategorianimi
    
    GROUP BY t2.kategorianimi
    Yours, Erik.
    ERIK RIKLUND :: Yes, I've been gone quite a while.

  3. #3
    SitePoint Member
    Join Date
    Jan 2005
    Location
    mikkeli
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by lilleman
    Hi,

    I must admit that this is a wild guess...

    Code:
    SELECT t1.* FROM ajanvaraus as t1
      INNER JOIN toimenpide as t2
         ON t2.toimenpidetunnus = $tp
      INNER JOIN toimenpide as t3
         ON t3.toimenpidetunnus
          = t1.toimenpidetunnus
        AND t3.kategorianimi
          = t2.kategorianimi
    
    GROUP BY t2.kategorianimi
    Yours, Erik.

    Thanx for your reply Erik but your wild guess didnt work

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select * 
      from ajanvaraus 
    inner
      join toimenpide
        on ajanvaraus.toimenpidetunnus 
         = toimenpide.toimenpidetunnus 
     where toimenpide.toimenpidetunnus = $tp
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •