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

    Unanswered: Find rows that dont have links

    Code:
    SELECT DISTINCT t1.ID 
    FROM `tbl1` t1, `tbl2` t2 
    WHERE t1.ID = t2.ID
    There are suppossed to be 300 rows in tbl1 and 3000 in tbl2.
    Each row in tbl2 will have an id pointing to tbl1. Every 10 rows in tbl2 link to one row in tbl1.
    But the query displays 288 rows - not 300. Therefore, I want the opposite of this. I want IDs which dont have 'links'.

    Thanks

  2. #2
    Join Date
    Aug 2003
    Location
    West
    Posts
    101
    There are suppossed to be 300 rows in tbl1 and 3000 in tbl2.
    Each row in tbl2 will have an id pointing to tbl1. Every 10 rows in tbl2 link to one row in tbl1.
    But the query displays 288 rows - not 300. Therefore, I want the opposite of this. I want IDs which dont have 'links'.

    select * from tbl1 where t1.id not in (select t2.id from tbl2);

  3. #3
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161
    Im so glad you got my question !
    Code:
    SELECT * FROM `tbl1` t1
    WHERE t1.id NOT IN (SELECT t2.id from `tbl2` t2);
    returns error.
    I know Im close.

    BTW, version 4.0.18 supports sub-selects right ?

  4. #4
    Join Date
    Aug 2003
    Location
    West
    Posts
    101
    I haven't worked with that version for a long time, but it should work. What is your error?

  5. #5
    Join Date
    Aug 2003
    Location
    West
    Posts
    101
    No, I'm wrong - subqueries not suppported until version 4.1. You might try ...

    select distinct tbl1.* from tbl1
    left join
    tbl2 on tbl1.id=tbl2.id
    where (tbl2.id) is null;

  6. #6
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161
    Wow ! Thank you so very much - didnt know abt left join - btw, this is taking taking a long time to process though (using phpMyAdmin) - is this creating a temporary table ? If so, its definitely not showing up in phpMyAdmin.

    I downloaded MySQL 5.0 and was abt to install it later to try out sub-queries and other new features - glad I didnt, otherwise I wouldnt have got to know LEFT JOIN.

    Thanks
    Last edited by anjanesh; 01-12-06 at 12:45.

  7. #7
    Join Date
    Aug 2003
    Location
    West
    Posts
    101
    Joins like this can be slow becuase the whole table has to be scanned. When you try on version 5 you have other ways to optimize the join statment ...
    http://dev.mysql.com/doc/refman/5.0/...imization.html

Posting Permissions

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