Professional Web Applications Themes

how to create OUTER JOIN (non trivial) - MySQL

hello, i would like to display two tables. result should contain all values from left table and distinct values from table 2. total count of returned rows should be equal of total rows in table1. for example: table1 A B C table 2 1 2 3 4 5 Outer join returns: 1 A 1 B 1 C 2 A 2 B 2 C (and so on) my wish result: A 1 B 2 C 3 can you have any ideas? thank you in advance R....

  1. #1

    Default how to create OUTER JOIN (non trivial)

    hello,
    i would like to display two tables. result should contain all values from
    left table and distinct values from table 2. total count of returned rows
    should be equal of total rows in table1.

    for example:

    table1
    A
    B
    C

    table 2
    1
    2
    3
    4
    5

    Outer join returns:
    1 A
    1 B
    1 C
    2 A
    2 B
    2 C
    (and so on)

    my wish result:

    A 1
    B 2
    C 3

    can you have any ideas?
    thank you in advance
    R.


    aaooo54 Guest

  2. #2

    Default Re: how to create OUTER JOIN (non trivial)

    On Jul 23, 12:04 pm, "aaooo54" <onet.pl> wrote: 

    What condition are you using to decide that the result should be:

    A 1
    B 2
    C 3

    and not something else like:

    A 2
    B 3
    C 1

    ?

    ZeldorBlat Guest

  3. #3

    Default Re: how to create OUTER JOIN (non trivial)

    > What condition are you using to decide that the result should be: 

    there is does not matter. any unique combination of values from table 2 is
    fine for me.

    Table 2 in my solution stored new identifiers which meet some specific
    criterias. Table 1 contains new consumers. The goal of query I try to find
    is attach to every new consumer one identifier. There is background of my
    question.

    regards,
    R


    aaooo54 Guest

  4. #4

    Default Re: how to create OUTER JOIN (non trivial)

    aaooo54 wrote: 
    >
    > there is does not matter. any unique combination of values from table
    > 2 is fine for me.
    >
    > Table 2 in my solution stored new identifiers which meet some specific
    > criterias. Table 1 contains new consumers. The goal of query I try to
    > find is attach to every new consumer one identifier. There is
    > background of my question.
    >
    > regards,
    > R[/ref]

    SET r1:=r2:=0;
    SELECT value1, value2 FROM (SELECT r1:=r1+1 r1, value1 FROM table1) AS t1
    LEFT JOIN (SELECT r2:=r2+1 r2, value2 FROM table2) AS t2 ON r2 = r1;


    Paul Guest

  5. #5

    Default Re: how to create OUTER JOIN (non trivial)

    hello Paul,
    there is a great solution! A key of success is: SELECT r1:=r1+1 as a
    simple method to mark every row by an incremental number.
    I will add this part of gold code to my diary:)
    thank you a lot!
    R
     


    aaooo54 Guest

Similar Threads

  1. Outer join?
    By Ignoramus23298 in forum MySQL
    Replies: 5
    Last Post: May 22nd, 09:06 AM
  2. OUTER JOIN
    By jorgepino in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: May 23rd, 12:59 PM
  3. Replies: 2
    Last Post: September 18th, 09:59 PM
  4. outer join, cross join, union? DDL/sample data incl.
    By Kevin in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: August 5th, 08: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