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

    Join Date
    Mar 2000
    Rep Power
    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
    Niagara Falls (On the wrong side of the gorge)
    Rep Power
    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, 06: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, 06:44 PM
  3. Not sure where to put inner join....
    By ralphuk100 in forum PHP Development
    Replies: 15
    Last Post: September 19th, 2003, 05:27 PM
  4. Replies: 1
    Last Post: April 14th, 2003, 10:36 AM
  5. SELECT where data is comma delimited..
    By Robert_J_Sherman in forum MySQL Help
    Replies: 29
    Last Post: June 28th, 2001, 10:29 AM

IMN logo majestic logo threadwatch logo seochat tools logo