[Resolved] Rewriting subqueries as Joins for MySQL 4.0.20

bluwulf

I jus discovered that MySQL 4.0.20 and earlier doesn't support subqueries....something I took for granted in PostgreSQL....The problem is I don't particularly kno how to use Joins in fact I'm not even sure that Joins can be used to do what I want to (I hope they can be used or else I'm dead...or there is some alternative way)

Anyway here are the queries I'm trying to convert :

SELECT * FROM details_submitted WHERE details_submitted.node_ID = (SELECT nodes.node_ID FROM nodes WHERE nodes.node_name = 'NODE_NAME');

SELECT * FROM details_submitted WHERE details_submitted.user_ID = (SELECT users.user_ID FROM users WHERE users.user_fname = 'FRIST_NAME' AND users.user_lname = 'LAST_NAME');

There exist a 1 to many relationship between the nodes and users table and the details_submitted table i.e

1 node (may) map to 1 or many details_submitted
1 user (may) map to 1 or many details_submitted

node_ID and user_ID is the primary key in the nodes table and users table respectively. These two ID's are foreign keys in the details_submitted table

I would really apprecite the help...thanx

tsinka

Hi,

there is a section in the MySQL manual about that:

Rewriting Subqueries

Thomas

bluwulf

Originally posted by tsinka
Hi,

there is a section in the MySQL manual about that:

Rewriting Subqueries

Thomas

I already read the manual,well...that particular section of the manuall...I just didn't understand it...in particular how to exactly apply it to my queries....nevertheless I have got the query I need (I hope)....I will go try it out :

SQL Query:

select d.* 
  from details_submitted as d
inner
  join nodes as n
    on d.node_ID 
     = n.node_ID
   and n.node_name = 'NODE_NAME'

select d.* 
  from details_submitted as d
inner
  join users as u
    on d.user_ID 
     = u.user_ID
   and u.user_fname = 'FRIST_NAME' 
   and u.user_lname = 'LAST_NAME'

Thanx anyway...