#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2012
    Location
    UK
    Posts
    21
    Rep Power
    0

    Group 2 columns from same table


    From a single table I'm trying to take all the data from one column (gifter) then merge it with all the data from the other column (receiver), once that's done then I want to group the data. I'm not very good at explaining things so I drew out below so that it might clear things out.

    It's purely for a statistic so I don't want to merge them within MySQL. Any ideas or would this have to be done some other way?
    Code:
    ---------------------------------
    |      TABLE NAME:  Gifts       |
    ---------------------------------
    | Gifter (INT) | Receiver (INT) |
    ---------------------------------
    |      1       |       2        |
    |      2       |       1        |
    |      3       |       4        |
    |      2       |       5        |
    |      2       |       3        |
    |      4       |       3        |
    ---------------------------------
    
           |     Merge     |
           -----------------
                   |
    
            ---------------
            | Data merged |
            ---------------
            |      1      |
            |      2      |
            |      3      |
            |      2      |
            |      2      |
            |      4      |
            |      2      |
            |      1      |
            |      4      |
            |      5      |
            |      3      |
            |      3      |
            ---------------
    
                   | GROUP
    
            ---------------
            | Data group  |
            ---------------
            |      1      |
            |      2      |
            |      3      |
            |      4      |
            |      5      |
            ---------------
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,486
    Rep Power
    1752
    Sounds like a job for a UNION
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2012
    Location
    UK
    Posts
    21
    Rep Power
    0
    I was thinking of trying out a Union but for some reason I can't seem to work it out for the life of me, first time I'm trying out a union, so far I have something like this:
    PHP Code:
    SELECT user
    FROM 
    (
        
    SELECT gifter AS user
        FROM gifts
    )
    UNION ALL
    (
        
    SELECT receiver AS user
        FROM gifts
    )
    GROUP BY user 
    Edit: Nevermind I managed to solve it like this:
    PHP Code:
    SELECT gifter AS user
    FROM gifts
                
    UNION
                
    SELECT receiver 
    AS user
    FROM gifts

    GROUP BY user 
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by Varsh
    Edit: Nevermind I managed to solve it like this:
    since you used UNION instead of UNION ALL (the correct choice, by the way), GROUP BY is redundant
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2012
    Location
    UK
    Posts
    21
    Rep Power
    0
    Ah didn't know that UNION automatically grouped the results together, that's really handy. Thanks for the advice.

IMN logo majestic logo threadwatch logo seochat tools logo