Professional Web Applications Themes

Joining 2 columns from the same table - MySQL

Hello, i have a table (Matches) than have the columns listed below: matchId, homeTeam, awayTeam, result I want to list all the teams DISTINCT both home and away teams. How do i do that? Best regards, Nicos...

  1. #1

    Default Joining 2 columns from the same table

    Hello,
    i have a table (Matches) than have the columns listed below:
    matchId, homeTeam, awayTeam, result

    I want to list all the teams DISTINCT both home and away teams.
    How do i do that?

    Best regards,
    Nicos

    Nicos Guest

  2. #2

    Default Re: Joining 2 columns from the same table


    Nicos wrote: 

    Are you looking for something like:

    SELECT homeTeam FROM matches
    UNION
    SELECT awayTeam FROM tmatches

    Captain Guest

  3. #3

    Default Re: Joining 2 columns from the same table

    Nicos wrote: 

    Maybe something like:

    SELECT DISTINCT (homeTeam, awayTeam), result FROM Matches;

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  4. #4

    Default Re: Joining 2 columns from the same table

    In article <googlegroups.com>, Nicos
    says... 

    Maybe:

    SELECT DISTINCT `Team`
    FROM (
    SELECT `homeTeam` AS `Team`
    FROM `Matches`
    UNION
    SELECT `awayTeam` AS `Team`
    FROM `Matches`)
    --
    PleegWat
    Remove caps to reply
    PleegWat Guest

  5. #5

    Default Re: Joining 2 columns from the same table


    PleegWat wrote: 
    >
    > Maybe:
    >
    > SELECT DISTINCT `Team`
    > FROM (
    > SELECT `homeTeam` AS `Team`
    > FROM `Matches`
    > UNION
    > SELECT `awayTeam` AS `Team`
    > FROM `Matches`)
    > --
    > PleegWat[/ref]

    That does the same as my ealier query as UNION keeps only distinct
    values (as opposed to UNION ALL which keeps all values)

    Captain Guest

  6. #6

    Default Re: Joining 2 columns from the same table


    PleegWat wrote: 
    >
    > Maybe:
    >
    > SELECT DISTINCT `Team`
    > FROM (
    > SELECT `homeTeam` AS `Team`
    > FROM `Matches`
    > UNION
    > SELECT `awayTeam` AS `Team`
    > FROM `Matches`)
    > --
    > PleegWat[/ref]

    That does the same as my ealier query as UNION keeps only distinct
    values (as opposed to UNION ALL which keeps all values)

    Captain Guest

  7. #7

    Default Re: Joining 2 columns from the same table

    In article <googlegroups.com>,
    Captain Paralytic says... 
    > >
    > > Maybe:
    > >
    > > SELECT DISTINCT `Team`
    > > FROM (
    > > SELECT `homeTeam` AS `Team`
    > > FROM `Matches`
    > > UNION
    > > SELECT `awayTeam` AS `Team`
    > > FROM `Matches`)
    > > --
    > > PleegWat[/ref]
    >
    > That does the same as my ealier query as UNION keeps only distinct
    > values (as opposed to UNION ALL which keeps all values)[/ref]

    Well, I knew the syntax, but I never actually used unions before. Didn't
    know it was distinct by default, assumed it wasn't since most stuff
    doesn't seem to be.
    --
    PleegWat
    Remove caps to reply
    PleegWat Guest

  8. #8

    Default Re: Joining 2 columns from the same table


    PleegWat wrote: 
    > >
    > > That does the same as my ealier query as UNION keeps only distinct
    > > values (as opposed to UNION ALL which keeps all values)[/ref]
    >
    > Well, I knew the syntax, but I never actually used unions before. Didn't
    > know it was distinct by default, assumed it wasn't since most stuff
    > doesn't seem to be.
    > --
    > PleegWat[/ref]
    That's the beauty of posting responses to other people's queries. You
    learn more when other's comment on your post. Admittedly some people
    correct others in a rather less than sympathetic manner, but the
    outcome of learning more still makes it worth it (IMHO).

    Captain Guest

Similar Threads

  1. Large table join using "text" as the joining key
    By howachen@gmail.com in forum MySQL
    Replies: 4
    Last Post: September 5th, 03:08 PM
  2. reordering table columns
    By Snef in forum MySQL
    Replies: 2
    Last Post: August 9th, 04:19 PM
  3. queryNew() joining to physical table
    By DannoParker in forum Coldfusion Database Access
    Replies: 2
    Last Post: September 7th, 10:41 PM
  4. Joining Paths or joining two shapes
    By luke williams in forum Adobe Indesign Windows
    Replies: 4
    Last Post: June 26th, 03:45 PM
  5. table columns misbehving...please help
    By pulling_hair_out webforumsuser@macromedia.com in forum Macromedia Dynamic HTML
    Replies: 3
    Last Post: August 20th, 04:30 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