Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161

    Unanswered: Rows of tbl1 that arent present in tbl2

    Hi

    I got 2 tables and I want rows from tbl1 that ids arent present in tbl2.
    Code:
    SELECT `id`, `info`
    FROM `tbl1`
    WHERE `id` <> (SELECT `id` FROM `tbl2`)
    I get the error :
    #1242 - Subquery returns more than 1 row
    Any idea how to make the query work ?

    Thanks

  2. #2
    Join Date
    Mar 2006
    Posts
    2
    use
    where 'id' not in (select id........)

  3. #3
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161
    Ah...Thanks a lot !

    Edit : BTW, Where do I search for this in MySQL 3.x,4.x docs ? Im using ver 5.x on my PC but host has 3.23 and want to know of NOT IN is acceptable in 3.x and 4.x.
    Last edited by anjanesh; 03-22-06 at 22:45.

  4. #4
    Join Date
    Mar 2006
    Posts
    7
    use left join and is null to get what u want,
    like this:

    *************
    SELECT
    ID
    FROM
    TableA
    LEFT JOIN
    TableB
    ON
    (TableA.ID = TableB.ID)
    WHERE
    (TableB.ID IS NULL)
    **********************
    this is exectly what u need

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by anjanesh
    Ah...Thanks a lot !

    Edit : BTW, Where do I search for this in MySQL 3.x,4.x docs ? Im using ver 5.x on my PC but host has 3.23 and want to know of NOT IN is acceptable in 3.x and 4.x.
    it would seem sense to run the same version (certainly the same major version) as your ISP. so either develop on a version 3.23 MySQL, or find an ISP which supports version 5
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161
    Quote Originally Posted by gtkuntz
    use
    where 'id' not in (select id........)
    Code:
    SELECT `ID`, `info` FROM `tbl1` tbl1
    WHERE tbl1.`ID` NOT IN (SELECT `ID` FROM `tbl1`)
    is returning just 1 row when it should be returning more.
    Any idea why ?

    Using MySQL 5.0.22
    MySQL 5.1

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by anjanesh
    Any idea why ?
    yes, because you mentioned the wrong table in the subquery
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161
    Code:
    SELECT `ID`,`info` FROM `tbl1`
    WHERE `ID` NOT IN (SELECT `ID` FROM `tbl2`)
    returns 0
    MySQL 5.1

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, why don't you try benanat's solution
    Code:
    select tbl1.id
         , tbl1.info 
      from tbl1
    left outer
      join tbl2
        on tbl2.id = tbl1.id 
     where tbl2.id is null
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161
    Sometimes the JOIN statements are taking more than 10 secs to execute.
    benanat's solution did work though.

    Btw, Im executing all these in phpMyAdmin.
    MySQL 5.1

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    make sure that each id column has an index (note: if it's a primary key, it already has an index)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •