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.
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');
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?
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.
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.
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
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:
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:
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++
}
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.
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…
$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;
}
I do believe he uses ASP rather than PHP which is why I was just throwing together a generic walk through of that code.