Difficult MySQL question about Rewriting Subqueries as Joins

[tiina]

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

[lilleman]

Hi,

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

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.

[tiina]

Hi,

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

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

[r937]

select * 
  from ajanvaraus 
inner
  join toimenpide
    on ajanvaraus.toimenpidetunnus 
     = toimenpide.toimenpidetunnus 
 where toimenpide.toimenpidetunnus = $tp