选择DISTINCT并且JOIN不起作用

Question

I've been working on this for days... I'm just learning and I'm reading all the tutorials but this just will not work for me! I know the problem has many answers but I must be typing something wrong...

1KaraokeDJ = 1KaraokeDJcopy because I couldn't figure out the use of AS to create a temp for use in the JOIN with the DISTINCT in 1 SELECT

ID is primary unique key Artist, Title, Disc, Track, Brand are all needed in the output

When using DISTINCT Title, Brand the result only has those 2 columns So I figured I would have to JOIN ON ID so I could have the Distinct records and still have all columns... There is probably a better way to do this but that's all I could find out reading the various tutorials.

<?php ...
$searchterm="call me";
$searchresults=$db->query("select DISTINCT Title, Brand from 1KaraokeDJcopy JOIN 1KaraokeDJ ON 1KaraokeDJcopy.ID=1KaraokeDJ.ID where Title like '%$searchterm%' limit 100");

        if(mysqli_num_rows($searchresults) > 0) {
          while($descri=mysqli_fetch_object($searchresults)) { ?>
            <div class="reslt">
              <h3 id="results">
                <?php
                  echo $descri->Artist); echo " - ";
                  echo $descri->Title);
                ?>
              </h3>
              <p class="Description">
                <?php
                  echo $descri->Brand; echo " - ";
                  echo $descri->Disc); echo " - ";
                  echo $descri->Track;
                ?>
              </p>
              <hr>
            </div>
          <?php } ?>

I get no query with the above code (print_r($searchresults); is null)

$searchresults=$db->query("select * from 1KaraokeDJ where Title like '%$searchterm%' order by Artist, Disc limit 100");

Produces 21 results with 4 duplicates (duplicate Brand because same song on several Discs even though different Disc number ie CB5016-01 and CBEP467-1 but both Brand is Chartbuster)

$searchresults=$db->query("select DISTINCT Title, Brand from 1KaraokeDJ where Title like '%$searchterm%' limit 100");

Produces the correct 17 results but my output no longer contains Artist, Disc, Track

Table structure for table 1KaraokeDJ

CREATE TABLE IF NOT EXISTS `1KaraokeDJ` (
  `Disc` varchar(12) COLLATE latin1_general_ci NOT NULL,
  `Track` enum('01','02','03','04','05','06','07','08','09','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36') COLLATE latin1_general_ci NOT NULL,
  `Artist` varchar(50) COLLATE latin1_general_ci NOT NULL,
  `Title` varchar(50) COLLATE latin1_general_ci NOT NULL,
  `Brand` enum('Chartbuster','DKKaraoke','Karaoke Hits','Sound Choice','Sunfly') COLLATE latin1_general_ci NOT NULL,
  `ID` smallint(5) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`ID`),
  KEY `Disc` (`Disc`),
  KEY `Artist` (`Artist`),
  KEY `Title` (`Title`),
  KEY `Brand` (`Brand`),
  FULLTEXT KEY `Artist_2` (`Artist`),
  FULLTEXT KEY `Title_2` (`Title`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=28618 ;

Answer 1

Step 0: Turn on error_reporting by adding this line to your PHP files

error_reporting(E_ALL); ini_set('display_errors', 'On');

Step 0.1: Try queries directly in MySQL if they are not working as desired.

1. I don't see why you need a join. You are joining the table to itself on ID=ID, which accomplishes nothing.

1.1 (You don't need to do this but for completeness) To JOIN a table to itself the syntax would be

SELECT * FROM table1 JOIN table1 as table1copy ON table1.col1 = table1copy.col2

1. Every column you want in the output needs to be in your SELECT clause.

   SELECT Artist, Title, Brand, Disc, Track FROM ...

2. Your not actually getting duplicates if there is even one column different. That's not a duplicate. I don't totally understand your desired result but i think GROUP BY might help.