SitePoint Sponsor

User Tag List

Page 2 of 2 FirstFirst 12
Results 26 to 28 of 28
  1. #26
    SitePoint Guru worchyld's Avatar
    Join Date
    Jul 2003
    Location
    Newcastle upon Tyne
    Posts
    909
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay, well I've tried the GROUP BY, it almost works.

    I've added a DISTINCT command to prevent duplication of rows being outputted;

    (tie this to the top of your code):
    Code:
    select distinct
    and following your idea, I added a GROUP BY command to the bottom of the code.

    (tie this to the bottom of your code):
    Code:
    	    GROUP BY 
    		t1.user_id_2";
    This groups data how you want it. But my output is incorrect, and I think it must have something to do with the big SQL command you wrote.

    As you can see, it groups the data, but it's outputting somebody who shouldn't be there.. Alice.
    quickMale is matched with sarah

    quickMale is matched with Olga

    quickMale is matched with Charlie

    quickMale is matched with Oisan

    quickMale is matched with helen

    quickMale is matched with Jenni

    quickMale is matched with Alice

    quickMale is matched with Noella

    quickMale is matched with Emma

    quickMale is matched with Lillie

    Alice is matched with quickMale

    quickMale is matched with quickFemale
    There's a problem using GROUP BY anyway.

    Because I've used GROUP BY on user_id_2, it'll filter out everybody else.

    This means I cannot email anybody else in this loop other than, in this example, quickMale.

    Surely there must be a way of acheiving this.

    I've been thinking about creating a temporary table which all the output from your super sql command is placed.

    Then we can use another query to find all distinct occurances of user_id_1 and output his/her matches.

    Would that work? How do you suggest solving this?

  2. #27
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    if you added a GROUP BY to my query, and then a DISTINCT, then you didn't understand what i've been trying to tell you

    the query is supposed to return results that look like this -

    userA user2
    userA user3
    userA user4
    userA user6
    userA user8
    userB user7
    userB user9
    userC user12
    userD user21
    userD user22

    you then have to process this in an output loop in your scripting language
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #28
    SitePoint Guru worchyld's Avatar
    Join Date
    Jul 2003
    Location
    Newcastle upon Tyne
    Posts
    909
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh, my mistake, I'm sorry.

    I'm going to start again with your original code and see if I can get a work-around.

    Many thanks for putting up with me.


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
  •