#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2000
    Posts
    7
    Rep Power
    0
    can someone help me to convert the following
    subquery to common inner join statement:

    SELECT table1.id, table1.desc FROM table1
    WHERE table1.id IN
    ( SELECT table2.id, table2.desc
    FROM table2
    WHERE table2.id = table1.id AND
    table2.desc LIKE "%keyword%"
    )

    the second SQL statement may result multiple rows but i don't want the side effeect which cartesian multiplication generated by inner join, i only want one!

    thank you.
  2. #2
  3. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    select table1.id,table1.desc as desc1,table2.desc as desc2 from table1,table2 where table1.id=table2.id and desc2 like "%keyword%";

    Just a quick note, desc is not allowed as a column name in mysql.

Similar Threads

  1. MSACCESS:Using a left join to join a table to a subquery?
    By Handsy11 in forum Database Management
    Replies: 1
    Last Post: May 5th, 2004, 07:06 PM
  2. Why is outer join so much slower than inner join?
    By VitalyB in forum MySQL Help
    Replies: 2
    Last Post: October 11th, 2003, 07:44 PM
  3. Not sure where to put inner join....
    By ralphuk100 in forum PHP Development
    Replies: 15
    Last Post: September 19th, 2003, 06:27 PM
  4. Replies: 1
    Last Post: April 14th, 2003, 11:36 AM
  5. SELECT where data is comma delimited..
    By Robert_J_Sherman in forum MySQL Help
    Replies: 29
    Last Post: June 28th, 2001, 11:29 AM

IMN logo majestic logo threadwatch logo seochat tools logo