Professional Web Applications Themes

joing query - MySQL

Hi All, I have a question for clearer brains than mine. I would like to join two tables,. There may be many possible joins in table B to table A, but I only want to join one row from B to table A - the row with the closest, but lesser, date. TABLE A Row Id date 1 46 3 Jan 7 20 10 Jan TABLE B Row Id date 4 46 1 Jan 5 46 2 Jan 6 46 4 Jan 8 20 8 Jan 10 20 7 Jan 11 20 9 jan Result Row 1` in A is ...

  1. #1

    Default joing query

    Hi All,

    I have a question for clearer brains than mine. I would like to join
    two tables,. There may be many possible joins in table B to table A,
    but I only want to join one row from B to table A - the row with the
    closest, but lesser, date.


    TABLE A
    Row Id date
    1 46 3 Jan
    7 20 10 Jan

    TABLE B
    Row Id date
    4 46 1 Jan
    5 46 2 Jan
    6 46 4 Jan
    8 20 8 Jan
    10 20 7 Jan
    11 20 9 jan

    Result
    Row 1` in A is joined to row 5 in B
    Row 7` in A is joined to row 11 in B

    SELECT
    a.row,
    b.row
    FROM
    A a
    LEFT JOIN B b
    ON (a.id = b.id AND a.date > b.date AND <the row with the max dates
    from the possible join in b>?)

    Any suggestions would be appreciated.

    Terry

    terryintransit@yahoo.com Guest

  2. #2

    Default Re: joing query

    This should be what you're looking for:

    mysql> SELECT * FROM a;
    +-----+----+------------+
    | row | id | d |
    +-----+----+------------+
    | 1 | 46 | 2006-01-03 |
    | 7 | 20 | 2006-01-10 |
    +-----+----+------------+
    2 rows in set (0.00 sec)

    mysql> SELECT * FROM b;
    +-----+----+------------+
    | row | id | d |
    +-----+----+------------+
    | 4 | 46 | 2006-01-01 |
    | 5 | 46 | 2006-01-02 |
    | 6 | 46 | 2006-01-04 |
    | 8 | 20 | 2006-01-08 |
    | 10 | 20 | 2006-01-07 |
    | 11 | 20 | 2006-01-09 |
    +-----+----+------------+
    6 rows in set (0.01 sec)

    mysql> SELECT c.row as row_a, b.row as row_b, c.max_d
    -> FROM b INNER JOIN
    -> (SELECT a.row, max(b.d) as max_d
    -> FROM a INNER JOIN b
    -> ON a.id = b.id AND b.d < a.d
    -> GROUP BY a.row) AS c
    -> ON b.d = c.max_d;
    +-------+-------+------------+
    | row_a | row_b | max_d |
    +-------+-------+------------+
    | 1 | 5 | 2006-01-02 |
    | 7 | 11 | 2006-01-09 |
    +-------+-------+------------+
    2 rows in set (0.01 sec)

    Markus


    Markus Popp Guest

Similar Threads

  1. Joing 2 table multiple times
    By phamtum in forum Coldfusion Database Access
    Replies: 1
    Last Post: July 15th, 12:50 PM
  2. Query of Queries on query New type query
    By david_h in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 6th, 08:55 PM
  3. Syntax for joing tables
    By William E Hatto in forum ASP Database
    Replies: 4
    Last Post: July 6th, 03:15 PM
  4. joing xp to 2000 domain
    By Joe in forum Windows Networking
    Replies: 2
    Last Post: July 2nd, 04:26 PM
  5. Replies: 1
    Last Post: July 2nd, 09:09 AM

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