Professional Web Applications Themes

How to get All Records in Table A that don't havematching ID in Table B - Coldfusion Database Access

I thought this would be a breeze, but I cannot seem to get it to work. I have two tables (Table A) and (Table B). (Table A) = all productid's (15,000 records) (Table B) = Limited # of productid's (12,700 records) I have had no success writing a query that will return ONLY the records from Table A that do not exist in Table B. I have tried sub-queries and "Not Equal" Joins but both seem to hang or crash MySQL. Does anyone know of code that would work or is the amount of records to compare too large? Thank ...

  1. #1

    Default How to get All Records in Table A that don't havematching ID in Table B

    I thought this would be a breeze, but I cannot seem to get it to work.

    I have two tables (Table A) and (Table B).
    (Table A) = all productid's (15,000 records)
    (Table B) = Limited # of productid's (12,700 records)

    I have had no success writing a query that will return ONLY the records from
    Table A that do not exist in Table B. I have tried sub-queries and "Not Equal"
    Joins but both seem to hang or crash MySQL. Does anyone know of code that
    would work or is the amount of records to compare too large?

    Thank you in advance.

    Ron
    [email]insight32aol.com[/email]

    Photoshare Guest

  2. #2

    Default Re: How to get All Records in Table A that don't havematching ID in Table B

    Try this SQL

    select * from tableA where id not in(select id from tableB)
    reenaroy Guest

  3. #3

    Default Re: How to get All Records in Table A that don't havematching ID in Table B

    That example will work but might be pretty slow depending on DB, indexes, etc.
    Your mission is usually accomplished with a "left join". Syntax depends on
    your DB, for example, Oracle doesn't even use the Join keyword, they prefer a
    (+) in the WHERE clause. If it's taking a long time, try a Left Join,
    something like:

    Select whatever from tblA
    LEFT JOIN tblB on tblA.ID = tblB.ID
    WHERE tblB.anycolumnatall IS NULL

    JMGibson3 Guest

  4. #4

    Default Re: How to get All Records in Table A that don't havematching ID in Table B

    Originally posted by: JMGibson3
    That example will work but might be pretty slow depending on DB, indexes, etc.
    Your mission is usually accomplished with a "left join". Syntax depends on
    your DB, for example, Oracle doesn't even use the Join keyword, they prefer a
    (+) in the WHERE clause. If it's taking a long time, try a Left Join,
    something like:

    Select whatever from tblA
    LEFT JOIN tblB on tblA.ID = tblB.ID
    WHERE tblB.anycolumnatall IS NULL

    Oracle started supporting the join keyword with version 9i.

    Your point about speed is valid though.



    Dan Bracuk Guest

  5. #5

    Default Re: How to get All Records in Table A that don't havematching ID in Table B

    What version of MySQL are you using? Older versions (before 4.1) did not allow
    subqueries, which would explain a lot. If you are version 4.1 or later, then I
    recommend using NOT EXISTS rather than NOT IN, as this is generally more
    efficient (at least it is in Oracle).

    Example:
    SELECT a.productID, a.other_fields
    FROM tableA a
    WHERE NOT EXISTS( SELECT 1
    FROM tableB b
    WHERE a.productID = b.productID)

    Phil

    paross1 Guest

Similar Threads

  1. recordcount 0 but records in table
    By speedyG in forum Macromedia ColdFusion
    Replies: 1
    Last Post: June 3rd, 05:19 PM
  2. No records found for MySQL Table
    By wittsdd in forum Coldfusion Database Access
    Replies: 4
    Last Post: April 9th, 12:54 AM
  3. Replies: 2
    Last Post: August 12th, 07:55 AM
  4. How to find duplicate records in the table?
    By mac in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 7th, 07:52 PM
  5. Delete from one table with matching records in another table
    By Dale Fye in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: July 1st, 01:28 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139