Professional Web Applications Themes

Help with JOIN and arithmetic - MySQL

I've never used JOIN, and have tried getting some good examples on the web, but am getting more confused. I have two tables. In the first table, I have a date field (among others) that represents the last date some event occurred. In the second table, I have another date field that represents when this particular record was created. Each time such an event occurs for the appropriate record in the first table, a new record is created in the second table with the date it occurred, and the date field in the first table is updated with the same ...

  1. #1

    Default Help with JOIN and arithmetic

    I've never used JOIN, and have tried getting some good examples on the
    web, but am getting more confused.

    I have two tables. In the first table, I have a date field (among
    others) that represents the last date some event occurred. In the
    second table, I have another date field that represents when this
    particular record was created. Each time such an event occurs for the
    appropriate record in the first table, a new record is created in the
    second table with the date it occurred, and the date field in the first
    table is updated with the same date. What I want to do is have an SQL
    statement that queries both tables for a difference in dates over a
    given period.

    For example:

    table1
    id varchar(5)
    lastFound date

    table2
    id varchar(5)
    seq int(5) autoincrement
    created date

    And I want to find those records where "table1.lastFound -
    table2.created > 60" AND "table2.seq = 1".

    Thanks!

    spride@gmail.com Guest

  2. #2

    Default Re: Help with JOIN and arithmetic

    [email]spride[/email] wrote:
    > I've never used JOIN, and have tried getting some good examples on the
    > web, but am getting more confused.
    Joins are essential to using relational databases. It would be worth
    your time to make sure you understand joins. You can google for "sql
    join tutorial" or something like that.

    Or you can look for a good book. Here are a few web pages with book
    recommendations:

    [url]http://databases.about.com/cs/sql/tp/sqlbooks.htm[/url]
    [url]http://www.ocelot.ca/mysql.htm[/url]
    [url]http://theopensourcery.com/osbksql.htm[/url]
    > I want to find those records where "table1.lastFound -
    > table2.created > 60" AND "table2.seq = 1".
    Here are a couple of queries that should give you those results.

    SELECT ...
    FROM table1 JOIN table2 USING (id)
    WHERE TO_DAYS(table1.lastFound - table2.created) > 60
    AND table2.seq = 1

    SELECT ...
    FROM table1 JOIN table2 USING (id)
    WHERE table1.lastFound > table2.created + INTERVAL 60 DAY
    AND table2.seq = 1

    I'd prefer the latter, for performance reasons. If one has an index on
    table1.lastFound, this query can benefit from the index, while the
    former query can't.

    Regards,
    Bill K.
    Bill Karwin Guest

Similar Threads

  1. Is left-join faster then inner join?
    By howachen@gmail.com in forum MySQL
    Replies: 5
    Last Post: March 5th, 07:11 AM
  2. #26366 [Bgs]: Arithmetic Error with JS and PHP
    By JaiKMw at aol dot com in forum PHP Development
    Replies: 0
    Last Post: November 23rd, 08:02 PM
  3. #26366 [Opn->Bgs]: Arithmetic Error with JS and PHP
    By derick@php.net in forum PHP Development
    Replies: 0
    Last Post: November 23rd, 07:47 PM
  4. Date Arithmetic in SQL or 4GL
    By Jack A in forum Informix
    Replies: 6
    Last Post: August 16th, 04:00 PM
  5. IP arithmetic
    By James F. Hranicky in forum Ruby
    Replies: 2
    Last Post: July 5th, 09:44 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