Professional Web Applications Themes

Try this select! - MySQL

Hi all... So, take a look at this tables... table_one --------------------- USR_ID | USR_NAM | ---------|-----------| 1 | Name_one | ---------|-----------| . . . . . . table_two ------------------------ USR_ID | USR_ID_AGAIN | ---------|---------------| 1 | 2 | ---------|---------------| . . . . . . table_tree ---------------------------------- USR_ID | USR_EMAIL | ----------|----------------------| 1 | [email]Name_oneemail.com[/email] | ----------|----------------------| . . . . . . Now, select the USR_NAM and the USR_EMAIL from the USR_ID and USR_ID_AGAIN of table_two as one register like: ------------------------------------------------------------------- USR_NAM | USR_EMAIL | USR_NAM | USR_EMAIL | ---------|------------------|----------------|---------------------| Name_one |Name_oneemail.com| Name_two | [email]Name_twoemail.com[/email] | ---------|------------------|----------------|---------------------| I tried ...

  1. #1

    Default Try this select!

    Hi all...


    So, take a look at this tables...

    table_one
    ---------------------
    USR_ID | USR_NAM |
    ---------|-----------|
    1 | Name_one |
    ---------|-----------|
    . .
    . .
    . .
    table_two
    ------------------------
    USR_ID | USR_ID_AGAIN |
    ---------|---------------|
    1 | 2 |
    ---------|---------------|
    . .
    . .
    . .
    table_tree
    ----------------------------------
    USR_ID | USR_EMAIL |
    ----------|----------------------|
    1 | [email]Name_oneemail.com[/email] |
    ----------|----------------------|
    . .
    . .
    . .

    Now, select the USR_NAM and the USR_EMAIL from the USR_ID and
    USR_ID_AGAIN of table_two as one register like:

    -------------------------------------------------------------------
    USR_NAM | USR_EMAIL | USR_NAM | USR_EMAIL |
    ---------|------------------|----------------|---------------------|
    Name_one |Name_oneemail.com| Name_two | [email]Name_twoemail.com[/email] |
    ---------|------------------|----------------|---------------------|


    I tried some join stuff but nothing works....

    any idea?

    joealey2003@yahoo.com Guest

  2. #2

    Default Re: Try this select!

    [email]joealey2003[/email] wrote:
    > Hi all...
    >
    >
    > So, take a look at this tables...
    >
    > table_one
    > ---------------------
    > USR_ID | USR_NAM |
    > ---------|-----------|
    > 1 | Name_one |
    > ---------|-----------|
    > . .
    > . .
    > . .
    > table_two
    > ------------------------
    > USR_ID | USR_ID_AGAIN |
    > ---------|---------------|
    > 1 | 2 |
    > ---------|---------------|
    > . .
    > . .
    > . .
    > table_tree
    > ----------------------------------
    > USR_ID | USR_EMAIL |
    > ----------|----------------------|
    > 1 | [email]Name_oneemail.com[/email] |
    > ----------|----------------------|
    > . .
    > . .
    > . .
    >
    > Now, select the USR_NAM and the USR_EMAIL from the USR_ID and
    > USR_ID_AGAIN of table_two as one register like:
    >
    > -------------------------------------------------------------------
    > USR_NAM | USR_EMAIL | USR_NAM | USR_EMAIL |
    > ---------|------------------|----------------|---------------------|
    > Name_one |Name_oneemail.com| Name_two | [email]Name_twoemail.com[/email] |
    > ---------|------------------|----------------|---------------------|
    >
    >
    > I tried some join stuff but nothing works....
    >
    > any idea?
    There is an awful lot left unsaid about how these tables will be populated,
    so I have made the following assumptions:
    1) Every entry in table_one has an entry in table_tree (sic)
    2) Every entry in table_one has a cross reference entry in table_two

    The above assumptions may not be correct for your data, but if not then
    there is more info you need to give us on required outputs.

    Note that in my query I have called table_tree table_three

    SELECT a.USR_NAM, b.USR_EMAIL, c.USR_NAM, d.USR_EMAIL
    FROM table_one a
    JOIN table_three b ON a.USR_ID = b.USR_ID
    JOIN table_two q ON a.USR_ID = q.USR_ID
    JOIN table_one c ON q.USR_ID_AGAIN = c.USR_ID
    JOIN table_three d ON c.USR_ID = d.USR_ID


    Paul Lautman Guest

  3. #3

    Default Re: Try this select!

    Paul Lautman wrote:
    > SELECT a.USR_NAM, b.USR_EMAIL, c.USR_NAM, d.USR_EMAIL
    > FROM table_one a
    > JOIN table_three b ON a.USR_ID = b.USR_ID
    > JOIN table_two q ON a.USR_ID = q.USR_ID
    > JOIN table_one c ON q.USR_ID_AGAIN = c.USR_ID
    > JOIN table_three d ON c.USR_ID = d.USR_ID
    The above could also be written as:
    SELECT a.USR_NAM, b.USR_EMAIL, c.USR_NAM, d.USR_EMAIL
    FROM table_one a
    JOIN (
    table_three b, table_two q, table_one c, table_three d
    ) ON ( a.USR_ID = b.USR_ID
    AND a.USR_ID = q.USR_ID
    AND q.USR_ID_AGAIN = c.USR_ID
    AND c.USR_ID = d.USR_ID )


    Paul Lautman Guest

  4. #4

    Default Re: Try this select!

    Paul Lautman wrote:
    > There is an awful lot left unsaid about how these tables will be populated,
    > so I have made the following assumptions:
    > 1) Every entry in table_one has an entry in table_tree (sic)
    Yes
    > 2) Every entry in table_one has a cross reference entry in table_two
    > Yes
    > The above assumptions may not be correct for your data, but if not then
    > there is more info you need to give us on required outputs.
    >
    > Note that in my query I have called table_tree table_three
    >
    > SELECT a.USR_NAM, b.USR_EMAIL, c.USR_NAM, d.USR_EMAIL
    > FROM table_one a
    > JOIN table_three b ON a.USR_ID = b.USR_ID
    > JOIN table_two q ON a.USR_ID = q.USR_ID
    > JOIN table_one c ON q.USR_ID_AGAIN = c.USR_ID
    > JOIN table_three d ON c.USR_ID = d.USR_ID
    Perfect!

    You got it!

    joealey2003@yahoo.com Guest

Similar Threads

  1. Select a list of items into an aliased field when doinga select
    By ehaemmerle in forum Coldfusion Database Access
    Replies: 3
    Last Post: March 18th, 10:49 PM
  2. Replies: 0
    Last Post: September 24th, 03:24 AM
  3. Replies: 0
    Last Post: September 11th, 11:26 AM
  4. Replies: 0
    Last Post: September 11th, 12:19 AM
  5. Replies: 0
    Last Post: April 15th, 01:22 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