Hello guys!
I need your help.
This is my query in db mysql.
My problem is the output of the query.
I need:
- not repeat the values in the columns `hits1` and `hits2`: in output `hits2` i have in this case twice Celtic value.
- if value is present in the columns `hits1`, the same value must not be present in the the columns `hits2` and vice versa.
- 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');
Reply With Quote
Originally Posted by K. Wolfe
Bookmarks