Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Posts
    10

    Unanswered: "A different B" How to SELECT data in the SQL language.

    I have 2 tables.

    "TABLE_A" has "TA_ID" field
    The value in this field is 1 , 2 , 3 , 4 , 5 , 6 and 7

    and

    "TABLE_B" has "TB_ID" field
    The value in this field is 1 , 2 , 3 and 8

    TABLE_A.TA_ID different TABLE_B.TB_ID is 4 , 5 and 6 (follow to mathematics)

    How to? i want to select that data in the SQL language by use SELECT command.

    Thanks

  2. #2
    Join Date
    Sep 2003
    Location
    Brussel
    Posts
    52

    Re: "A different B" How to SELECT data in the SQL language.

    So you like to select the data that is in table A but not in table B. Right?

    SELECT DISTINCT ta_id
    FROM table_a
    WHERE ta_id NOT IN
    (SELECT tb_id
    FROM table_b);


    It is possible that you can do it easier, but this works to.
    A good programmer is a LAZY programmer!

  3. #3
    Join Date
    Sep 2003
    Location
    London
    Posts
    56

    Re: "A different B" How to SELECT data in the SQL language.

    Originally posted by Lazy
    So you like to select the data that is in table A but not in table B. Right?

    SELECT DISTINCT ta_id
    FROM table_a
    WHERE ta_id NOT IN
    (SELECT tb_id
    FROM table_b);


    It is possible that you can do it easier, but this works to.
    Note that MySQL does not support subqueries, so unless you are using the latest alpha release, this will not work.

  4. #4
    Join Date
    Jul 2003
    Posts
    35
    Hi,

    You could try the following without subselects...

    SELECT ta.id from table_a AS ta LEFT JOIN
    table_b AS tb ON ta.id = tb.id
    WHERE tb.id IS NULL;

    This will display something like...

    +------+
    | id |
    +------+
    | 4 |
    | 5 |
    | 6 |
    | 7 |
    +------+
    4 rows in set (0.00 sec)

    cheers,
    ash

  5. #5
    Join Date
    Oct 2003
    Posts
    10
    Thank you asherh

    Your script is correct. i tried that script.

    If the TABLE_B have small records. This script is work.
    But, if the TABLE_B have many records.
    The script will be run long time so much.
    and display "Fatal error: Maximum execution time of 30 seconds exceeded in"
    it's over time.

  6. #6
    Join Date
    Sep 2003
    Location
    London
    Posts
    56
    Are there indexes on the 2 id columns?

  7. #7
    Join Date
    Oct 2003
    Posts
    10
    No, The 2 id column are not primary and index.

    What is a problem?

Posting Permissions

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