Professional Web Applications Themes

Mysql Select, two tables. - MySQL

Hi! I need help to do this thing, if possible in SQL Table 1 ID | xpto | Cliente ---------------------------- 1 | rerere | 12345 Table 2 ID | Cliente | c1 | c2 | date -------------------------------------- 1 | 12345 | abcd | abcd | 2006-08-30 2 | 12345 | abcdef | abgh | 2006-09-01 so i want the table 1 with the last row from table 2 (i.e. with the most recente date) Resulta table t1.ID | t1.xpto | t1.Cliente | t2.ID | t2.Cliente | t2.c1 | t2.date -------------------------------------------------------------------------------------- 1 | rerere | 12345 | 2 | 12345 | ...

  1. #1

    Default Mysql Select, two tables.

    Hi!

    I need help to do this thing, if possible in SQL

    Table 1
    ID | xpto | Cliente
    ----------------------------
    1 | rerere | 12345

    Table 2
    ID | Cliente | c1 | c2 | date
    --------------------------------------
    1 | 12345 | abcd | abcd | 2006-08-30
    2 | 12345 | abcdef | abgh | 2006-09-01

    so i want the table 1 with the last row from table 2 (i.e. with the
    most recente date)
    Resulta table
    t1.ID | t1.xpto | t1.Cliente | t2.ID | t2.Cliente | t2.c1 | t2.date
    --------------------------------------------------------------------------------------
    1 | rerere | 12345 | 2 | 12345 | abgh | 2006-09-01

    Now imagine this to many rows. I only want info regard the cliente from
    table 2 that is the moste recent.

    Can you help me?

    Thanks in advance Pedro Coelho

    Pedro Guest

  2. #2

    Default Re: Mysql Select, two tables.


    Pedro Coelho wrote: 

    Untested (sometimes I get a & b mixed up!):

    SELECT t1.ID,t1.xpto,t1.Cliente,t2.ID,t2.Cliente,t2.c1,t2 .date
    FROM
    (SELECT table2.*
    FROM table2 a
    LEFT JOIN table2 b ON a.Cliente = b.Cliente
    AND a.ID <> b.ID
    AND a.date < b.date
    WHERE b.date IS NULL) t2
    LEFT JOIN table1 t1
    ON t1.Cliente = t2.Cliente;

    strawberry Guest

  3. #3

    Default Re: Mysql Select, two tables.

    > Table 1 

    select table1.* , table2.* from table1,table2
    where table1.Cliente=table2.Cliente
    and table2.date = (select max(date) from table2);


    Regards
    Dimitre


    Radoulov, Guest

  4. #4

    Default Re: Mysql Select, two tables.

    Thanks that worked with a little change

    SELECT * FROM
    (SELECT a.*
    FROM clientes a
    LEFT JOIN clientes b ON (a.Cliente = b.Cliente
    AND a.ID_Cliente <> b.ID_Cliente
    AND a.Data < b.Data)
    WHERE b.Data IS NULL) t2
    LEFT JOIN taxas_negociadas t1
    ON t1.Cliente = t2.Cliente;

    there was a little "(" on the first join that was forgoted.

    strawberry escreveu: 

    Pedro Guest

  5. #5

    Default Re: Mysql Select, two tables.

    Hi!

    That almost worked, but only if all the clientes for intersect with the
    first table as an iqual date.

    It was simple, but did not do the job.

    Thanks.

    Radoulov, Dimitre escreveu: 
    >
    > select table1.* , table2.* from table1,table2
    > where table1.Cliente=table2.Cliente
    > and table2.date = (select max(date) from table2);
    >
    >
    > Regards
    > Dimitre[/ref]

    Pedro Guest

Similar Threads

  1. Select accoss 2 tables
    By ShowOffs.ca in forum PHP Development
    Replies: 0
    Last Post: June 11th, 07:32 PM
  2. Select from two tables
    By Taper Litwater in forum ASP
    Replies: 4
    Last Post: October 27th, 08:41 PM
  3. MySQL Q - SELECT with 2 tables
    By Geoff Berrow in forum PHP Development
    Replies: 4
    Last Post: September 5th, 07:17 AM
  4. SELECT DISTINCT from two tables
    By Marco Alting in forum ASP
    Replies: 4
    Last Post: July 31st, 10:26 AM
  5. Select quantity from 2 tables
    By Simon in forum Microsoft SQL / MS SQL Server
    Replies: 10
    Last Post: July 8th, 03:01 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