SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Complex SQL Query

    Hello guys!
    I need your help.
    This is my query in db mysql.

    My problem is the output of the query.
    I need:
    1. not repeat the values in the columns `hits1` and `hits2`: in output `hits2` i have in this case twice Celtic value.
    2. if value is present in the columns `hits1`, the same value must not be present in the the columns `hits2` and vice versa.
    3. these couples of records are not allowed:
      Milan - Málaga
      Juventus - Shakhtar Donetsk
      Juventus - Milan
      Real Madrid - Málaga
      Málaga - Barcelona
      Barcelona - Real Madrid
      Arsenal - Celtic
      Celtic - Man. United
      Man. United - Arsenal
      Schalke - Dortmund
      Dortmund - Bayern
      Bayern - Schalke

    How can i do it?
    Any help?
    Thank you.
    Code:
    mysql> SELECT DISTINCT
    	hits1,
    	hits2
    FROM
    	(
    		SELECT DISTINCT
    			x.strname hits1,
    			y.strname hits2
    		FROM
    			listing_names x
    		JOIN listing_names y ON y.id <> x.id
    		JOIN listing_names z ON z.id <> x.id
    		AND z.id <> y.id
    		ORDER BY
    			RAND()
    		LIMIT 8
    	) q;
    +------------------+------------------+
    | hits1            | hits2            |
    +------------------+------------------+
    | Schalke          | Celtic           |
    | Man. United      | Barcelona        |
    | Shakhtar Donetsk | Porto            |
    | Arsenal          | Celtic           |
    | Galatasaray      | Real Madrid      |
    | Milan            | Shakhtar Donetsk |
    | Porto            | Man. United      |
    | Celtic           | Schalke          |
    +------------------+------------------+
    8 rows in set
    
    
    SET FOREIGN_KEY_CHECKS=0;
    
    -- ----------------------------
    -- Table structure for `listing_names`
    -- ----------------------------
    DROP TABLE IF EXISTS `listing_names`;
    CREATE TABLE `listing_names` (
      `free` int(1) DEFAULT NULL,
      `strName` varchar(255) DEFAULT NULL,
      `id` int(10) NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1;
    
    -- ----------------------------
    -- Records of listing_names
    -- ----------------------------
    INSERT INTO `listing_names` VALUES ('1', 'Juventus', '1');
    INSERT INTO `listing_names` VALUES ('2', 'Real Madrid', '2');
    INSERT INTO `listing_names` VALUES ('3', 'Arsenal', '3');
    INSERT INTO `listing_names` VALUES ('0', 'Porto', '4');
    INSERT INTO `listing_names` VALUES ('2', 'Valencia', '5');
    INSERT INTO `listing_names` VALUES ('0', 'Galatasaray', '6');
    INSERT INTO `listing_names` VALUES ('3', 'Celtic', '7');
    INSERT INTO `listing_names` VALUES ('0', 'Shakhtar Donetsk', '8');
    INSERT INTO `listing_names` VALUES ('0', 'PSG', '9');
    INSERT INTO `listing_names` VALUES ('4', 'Schalke', '10');
    INSERT INTO `listing_names` VALUES ('4', 'Dortmund', '11');
    INSERT INTO `listing_names` VALUES ('2', 'Málaga', '12');
    INSERT INTO `listing_names` VALUES ('4', 'Bayern', '13');
    INSERT INTO `listing_names` VALUES ('2', 'Barcelona', '14');
    INSERT INTO `listing_names` VALUES ('3', 'Man. United', '15');
    INSERT INTO `listing_names` VALUES ('1', 'Milan', '16');

  2. #2
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,180
    Mentioned
    65 Post(s)
    Tagged
    2 Thread(s)
    So you have one table, 'listing_names', which has one column of interest 'strname', and you want to display any possible combination of two, without them equaling what you listed in point number 3? Do you mean that exact matchup? You listed Milan - Málaga, but is Málaga - Milan allowed?

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by K. Wolfe View Post
    ...is Málaga - Milan allowed?
    i would guess not, but good question
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    thank for you for answers.

    Quote Originally Posted by K. Wolfe View Post
    So you have one table, 'listing_names', which has one column of interest 'strname', and you want to display any possible combination of two, without them equaling what you listed in point number 3?
    Do you mean that exact matchup?
    Yes, Sir.

    You listed Milan - Málaga, but is Málaga - Milan allowed?
    No, not allowed `Milan - Málaga` or `Málaga - Milan` and all listed in point number 3.
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  5. #5
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,180
    Mentioned
    65 Post(s)
    Tagged
    2 Thread(s)
    Code:
    select
    a.strname as hits1, b.strname as hits2
    from
    listing_names a
    join listing_names b on a.id <> b.id
    where
    concat(a.strname, ' - ', b.strname) not in (
    'Milan - Málaga',
    'Juventus - Shakhtar Donetsk',
    'Juventus - Milan',
    'Real Madrid - Málaga',
    'Málaga - Barcelona',
    'Barcelona - Real Madrid',
    'Arsenal - Celtic',
    'Celtic - Man. United',
    'Man. United - Arsenal',
    'Schalke - Dortmund',
    'Dortmund - Bayern',
    'Bayern - Schalke' --im lazy, duplicate this but reversing your two words, ex. Málaga - Milan
    )
    group by 
    a.strname as hits1, b.strname as hits2
    I hate distinct. This would be the start of my approach. I've never written a query such as this that uses a does not equal in the join. I don't believe a left or inner would be used? r937 might have more to add on this.

  6. #6
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,180
    Mentioned
    65 Post(s)
    Tagged
    2 Thread(s)
    Also, let the auto increment do its job...

    Code:
    INSERT INTO `listing_names` VALUES ('1', 'Juventus');

  7. #7
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    thank you.

    this is the output:
    Code:
    mysql> SELECT
    	a.strname AS hits1,
    	b.strname AS hits2
    FROM
    	listing_names a
    JOIN listing_names b ON a.id <> b.id
    WHERE
    	concat(a.strname, ' - ', b.strname) NOT IN (
    		'Milan - Málaga',
    		'Juventus - Shakhtar Donetsk',
    		'Juventus - Milan',
    		'Real Madrid - Málaga',
    		'Málaga - Barcelona',
    		'Barcelona - Real Madrid',
    		'Arsenal - Celtic',
    		'Celtic - Man. United',
    		'Man. United - Arsenal',
    		'Schalke - Dortmund',
    		'Dortmund - Bayern',
    		'Bayern - Schalke'
    	)
    GROUP BY
    	hits1,
    	hits2;
    +------------------+------------------+
    | hits1            | hits2            |
    +------------------+------------------+
    | Arsenal          | Barcelona        |
    | Arsenal          | Bayern           |
    | Arsenal          | Dortmund         |
    | Arsenal          | Galatasaray      |
    | Arsenal          | Juventus         |
    | Arsenal          | Málaga           |
    | Arsenal          | Man. United      |
    | Arsenal          | Milan            |
    | Arsenal          | Porto            |
    | Arsenal          | PSG              |
    | Arsenal          | Real Madrid      |
    | Arsenal          | Schalke          |
    | Arsenal          | Shakhtar Donetsk |
    | Arsenal          | Valencia         |
    | Barcelona        | Arsenal          |
    | Barcelona        | Bayern           |
    | Barcelona        | Celtic           |
    | Barcelona        | Dortmund         |
    | Barcelona        | Galatasaray      |
    | Barcelona        | Juventus         |
    | Barcelona        | Málaga           |
    | Barcelona        | Man. United      |
    | Barcelona        | Milan            |
    | Barcelona        | Porto            |
    | Barcelona        | PSG              |
    | Barcelona        | Schalke          |
    | Barcelona        | Shakhtar Donetsk |
    | Barcelona        | Valencia         |
    | Bayern           | Arsenal          |
    | Bayern           | Barcelona        |
    | Bayern           | Celtic           |
    | Bayern           | Dortmund         |
    | Bayern           | Galatasaray      |
    | Bayern           | Juventus         |
    | Bayern           | Málaga           |
    | Bayern           | Man. United      |
    | Bayern           | Milan            |
    | Bayern           | Porto            |
    | Bayern           | PSG              |
    | Bayern           | Real Madrid      |
    | Bayern           | Shakhtar Donetsk |
    | Bayern           | Valencia         |
    | Celtic           | Arsenal          |
    | Celtic           | Barcelona        |
    | Celtic           | Bayern           |
    | Celtic           | Dortmund         |
    | Celtic           | Galatasaray      |
    | Celtic           | Juventus         |
    | Celtic           | Málaga           |
    | Celtic           | Milan            |
    | Celtic           | Porto            |
    | Celtic           | PSG              |
    | Celtic           | Real Madrid      |
    | Celtic           | Schalke          |
    | Celtic           | Shakhtar Donetsk |
    | Celtic           | Valencia         |
    | Dortmund         | Arsenal          |
    | Dortmund         | Barcelona        |
    | Dortmund         | Celtic           |
    | Dortmund         | Galatasaray      |
    | Dortmund         | Juventus         |
    | Dortmund         | Málaga           |
    | Dortmund         | Man. United      |
    | Dortmund         | Milan            |
    | Dortmund         | Porto            |
    | Dortmund         | PSG              |
    | Dortmund         | Real Madrid      |
    | Dortmund         | Schalke          |
    | Dortmund         | Shakhtar Donetsk |
    | Dortmund         | Valencia         |
    | Galatasaray      | Arsenal          |
    | Galatasaray      | Barcelona        |
    | Galatasaray      | Bayern           |
    | Galatasaray      | Celtic           |
    | Galatasaray      | Dortmund         |
    | Galatasaray      | Juventus         |
    | Galatasaray      | Málaga           |
    | Galatasaray      | Man. United      |
    | Galatasaray      | Milan            |
    | Galatasaray      | Porto            |
    | Galatasaray      | PSG              |
    | Galatasaray      | Real Madrid      |
    | Galatasaray      | Schalke          |
    | Galatasaray      | Shakhtar Donetsk |
    | Galatasaray      | Valencia         |
    | Juventus         | Arsenal          |
    | Juventus         | Barcelona        |
    | Juventus         | Bayern           |
    | Juventus         | Celtic           |
    | Juventus         | Dortmund         |
    | Juventus         | Galatasaray      |
    | Juventus         | Málaga           |
    | Juventus         | Man. United      |
    | Juventus         | Porto            |
    | Juventus         | PSG              |
    | Juventus         | Real Madrid      |
    | Juventus         | Schalke          |
    | Juventus         | Valencia         |
    | Málaga           | Arsenal          |
    | Málaga           | Bayern           |
    | Málaga           | Celtic           |
    | Málaga           | Dortmund         |
    | Málaga           | Galatasaray      |
    | Málaga           | Juventus         |
    | Málaga           | Man. United      |
    | Málaga           | Milan            |
    | Málaga           | Porto            |
    | Málaga           | PSG              |
    | Málaga           | Real Madrid      |
    | Málaga           | Schalke          |
    | Málaga           | Shakhtar Donetsk |
    | Málaga           | Valencia         |
    | Man. United      | Barcelona        |
    | Man. United      | Bayern           |
    | Man. United      | Celtic           |
    | Man. United      | Dortmund         |
    | Man. United      | Galatasaray      |
    | Man. United      | Juventus         |
    | Man. United      | Málaga           |
    | Man. United      | Milan            |
    | Man. United      | Porto            |
    | Man. United      | PSG              |
    | Man. United      | Real Madrid      |
    | Man. United      | Schalke          |
    | Man. United      | Shakhtar Donetsk |
    | Man. United      | Valencia         |
    | Milan            | Arsenal          |
    | Milan            | Barcelona        |
    | Milan            | Bayern           |
    | Milan            | Celtic           |
    | Milan            | Dortmund         |
    | Milan            | Galatasaray      |
    | Milan            | Juventus         |
    | Milan            | Man. United      |
    | Milan            | Porto            |
    | Milan            | PSG              |
    | Milan            | Real Madrid      |
    | Milan            | Schalke          |
    | Milan            | Shakhtar Donetsk |
    | Milan            | Valencia         |
    | Porto            | Arsenal          |
    | Porto            | Barcelona        |
    | Porto            | Bayern           |
    | Porto            | Celtic           |
    | Porto            | Dortmund         |
    | Porto            | Galatasaray      |
    | Porto            | Juventus         |
    | Porto            | Málaga           |
    | Porto            | Man. United      |
    | Porto            | Milan            |
    | Porto            | PSG              |
    | Porto            | Real Madrid      |
    | Porto            | Schalke          |
    | Porto            | Shakhtar Donetsk |
    | Porto            | Valencia         |
    | PSG              | Arsenal          |
    | PSG              | Barcelona        |
    | PSG              | Bayern           |
    | PSG              | Celtic           |
    | PSG              | Dortmund         |
    | PSG              | Galatasaray      |
    | PSG              | Juventus         |
    | PSG              | Málaga           |
    | PSG              | Man. United      |
    | PSG              | Milan            |
    | PSG              | Porto            |
    | PSG              | Real Madrid      |
    | PSG              | Schalke          |
    | PSG              | Shakhtar Donetsk |
    | PSG              | Valencia         |
    | Real Madrid      | Arsenal          |
    | Real Madrid      | Barcelona        |
    | Real Madrid      | Bayern           |
    | Real Madrid      | Celtic           |
    | Real Madrid      | Dortmund         |
    | Real Madrid      | Galatasaray      |
    | Real Madrid      | Juventus         |
    | Real Madrid      | Man. United      |
    | Real Madrid      | Milan            |
    | Real Madrid      | Porto            |
    | Real Madrid      | PSG              |
    | Real Madrid      | Schalke          |
    | Real Madrid      | Shakhtar Donetsk |
    | Real Madrid      | Valencia         |
    | Schalke          | Arsenal          |
    | Schalke          | Barcelona        |
    | Schalke          | Bayern           |
    | Schalke          | Celtic           |
    | Schalke          | Galatasaray      |
    | Schalke          | Juventus         |
    | Schalke          | Málaga           |
    | Schalke          | Man. United      |
    | Schalke          | Milan            |
    | Schalke          | Porto            |
    | Schalke          | PSG              |
    | Schalke          | Real Madrid      |
    | Schalke          | Shakhtar Donetsk |
    | Schalke          | Valencia         |
    | Shakhtar Donetsk | Arsenal          |
    | Shakhtar Donetsk | Barcelona        |
    | Shakhtar Donetsk | Bayern           |
    | Shakhtar Donetsk | Celtic           |
    | Shakhtar Donetsk | Dortmund         |
    | Shakhtar Donetsk | Galatasaray      |
    | Shakhtar Donetsk | Juventus         |
    | Shakhtar Donetsk | Málaga           |
    | Shakhtar Donetsk | Man. United      |
    | Shakhtar Donetsk | Milan            |
    | Shakhtar Donetsk | Porto            |
    | Shakhtar Donetsk | PSG              |
    | Shakhtar Donetsk | Real Madrid      |
    | Shakhtar Donetsk | Schalke          |
    | Shakhtar Donetsk | Valencia         |
    | Valencia         | Arsenal          |
    | Valencia         | Barcelona        |
    | Valencia         | Bayern           |
    | Valencia         | Celtic           |
    | Valencia         | Dortmund         |
    | Valencia         | Galatasaray      |
    | Valencia         | Juventus         |
    | Valencia         | Málaga           |
    | Valencia         | Man. United      |
    | Valencia         | Milan            |
    | Valencia         | Porto            |
    | Valencia         | PSG              |
    | Valencia         | Real Madrid      |
    | Valencia         | Schalke          |
    | Valencia         | Shakhtar Donetsk |
    +------------------+------------------+
    228 rows in set
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  8. #8
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,180
    Mentioned
    65 Post(s)
    Tagged
    2 Thread(s)
    looks good then? just throw in your limit and rand() order clause, and don't forget to duplicate those not in() lines that I didn't write out for you.

  9. #9
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by K. Wolfe View Post
    looks good then? just throw in your limit and rand() order clause, and don't forget to duplicate those not in() lines that I didn't write out for you.
    thank you.

    But in output `hits1` i have thrice Celtic value and in output `hits2` i have twice Galatasaray
    And I have values present in the columns `hits1` and the same value present in the columns `hits2`.
    Code:
    mysql> SELECT
    	a.strname AS hits1,
    	b.strname AS hits2
    FROM
    	listing_names a
    JOIN listing_names b ON a.id <> b.id
    WHERE
    	concat(a.strname, ' - ', b.strname) NOT IN (
    		'Milan - Málaga',
    		'Juventus - Shakhtar Donetsk',
    		'Juventus - Milan',
    		'Real Madrid - Málaga',
    		'Málaga - Barcelona',
    		'Barcelona - Real Madrid',
    		'Arsenal - Celtic',
    		'Celtic - Man. United',
    		'Man. United - Arsenal',
    		'Schalke - Dortmund',
    		'Dortmund - Bayern',
    		'Bayern - Schalke'
    	)
    GROUP BY
    	hits1,
    	hits2
    ORDER BY
    	RAND()
    LIMIT 8;
    +------------------+-------------+
    | hits1            | hits2       |
    +------------------+-------------+
    | Milan            | Galatasaray |
    | Celtic           | PSG         |
    | Barcelona        | Schalke     |
    | Celtic           | Valencia    |
    | Galatasaray      | Man. United |
    | Juventus         | Arsenal     |
    | Celtic           | Juventus    |
    | Shakhtar Donetsk | Galatasaray |
    +------------------+-------------+
    8 rows in set
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  10. #10
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,180
    Mentioned
    65 Post(s)
    Tagged
    2 Thread(s)
    Ah so a name can only be utilized once? Missed that one.

  11. #11
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by K. Wolfe View Post
    Ah so a name can only be utilized once? Missed that one.
    I may have asked something impossible...
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  12. #12
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,180
    Mentioned
    65 Post(s)
    Tagged
    2 Thread(s)
    After doing a little thinking on this, you'd have to resort to a server side language to store what's been utilized in an array, using this result of all possible combinations. I was thinking of doing a sub query to pick 1 random possibility from each hit1, but then you run into the problem of possibly randomly selecting a hit2 that's already been utilized earlier as hit 1.

    Stick with my code provided:

    Code:
    SELECT
        a.strname AS hits1,
        b.strname AS hits2
    FROM
        listing_names a
    JOIN listing_names b ON a.id <> b.id
    WHERE
        concat(a.strname, ' - ', b.strname) NOT IN (
            'Milan - Málaga',
            'Juventus - Shakhtar Donetsk',
            'Juventus - Milan',
            'Real Madrid - Málaga',
            'Málaga - Barcelona',
            'Barcelona - Real Madrid',
            'Arsenal - Celtic',
            'Celtic - Man. United',
            'Man. United - Arsenal',
            'Schalke - Dortmund',
            'Dortmund - Bayern',
            'Bayern - Schalke'
        )
    GROUP BY
        hits1,
        hits2;
    and create a php / asp loop to iterate through. heres a general concept:

    Code:
    i=0
    While i <= 8
    {
    //pick random row from result
    $rand = rand(x to count($record))
    array_push($eightResults) = $record[$rand][hit1] . " - " . $record[$rand][hit2]
    //remove rows from $record results array that contain hit1 and hit2 that were just picked
    i++
    }

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    getting very close now, kyle

    you still need to prevent generating both Schalke-Celtic and Celtic-Schalke

    and i'm pretty sure you don't need the GROUP BY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,180
    Mentioned
    65 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by r937 View Post
    you still need to prevent generating both Schalke-Celtic and Celtic-Schalke
    That's already taken care of, I already noted in the first example it needed to be done and I'm not rewriting all those by hand for him I do believe he uses ASP rather than PHP which is why I was just throwing together a generic walk through of that code.

  15. #15
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,180
    Mentioned
    65 Post(s)
    Tagged
    2 Thread(s)
    I hate looking at these, because then I can't stop thinking about them... If you wind up using a server side language then you should change the whole way you approach it:

    what I've got so far..
    Code:
    $source = sqlquery("select strname from listing_names group by strname;");
    $bannedPairs = array(
    'hit1' => 'Milan', 'hit2' => 'Málaga',
    'hit1' => 'Juventus', 'hit2' => 'Shakhtar Donetsk'
    );
    print_r(randPairs($source, $bannedPairs, 8));
    
    public function randPairs($source, $bannedPairs, $limit) {
    i=0
    While (i <= $limit)
    {
    //randomly select rows from $source
    //ensure $source['hit1'].$source['hit2'] AND $source['hit2'].$source['hit1'] not in $banList
    array_push($result, array('hit1' => $source['hit1'], 'hit2' => $source['hit2'])); //push to result
    //remove both from $source so they cant be used again
    i++
    }
    return $result;
    }
    thoughts?

  16. #16
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    thank you.

    My server side language is asp net C#
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye


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
  •