dou760663 2015-11-18 15:11 采纳率: 100%
浏览 79
已采纳

选择DISTINCT并且JOIN不起作用

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 ;
  • 写回答

3条回答 默认 最新

  • dongpanbo4727 2015-11-18 15:34
    关注

    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.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(2条)

报告相同问题?

悬赏问题

  • ¥15 矩阵加法的规则是两个矩阵中对应位置的数的绝对值进行加和
  • ¥15 活动选择题。最多可以参加几个项目?
  • ¥15 飞机曲面部件如机翼,壁板等具体的孔位模型
  • ¥15 vs2019中数据导出问题
  • ¥20 云服务Linux系统TCP-MSS值修改?
  • ¥20 关于#单片机#的问题:项目:使用模拟iic与ov2640通讯环境:F407问题:读取的ID号总是0xff,自己调了调发现在读从机数据时,SDA线上并未有信号变化(语言-c语言)
  • ¥20 怎么在stm32门禁成品上增加查询记录功能
  • ¥15 Source insight编写代码后使用CCS5.2版本import之后,代码跳到注释行里面
  • ¥50 NT4.0系统 STOP:0X0000007B
  • ¥15 想问一下stata17中这段代码哪里有问题呀